## Hot answers tagged worksheet-function

4

To keep it simple, just add following rows to your code before "strFilename = strOutputFolder & "\" & strItem":
strItem= replace(strItem, "~", "_")
strItem= replace(strItem, """, "_")
strItem= replace(strItem, "%", "_")
strItem= replace(strItem, "#", "_")
' and so long ...
strFilename = strOutputFolder & "\" & strItem
[UPDATED]
Well, as ...

2

You need to use sumif with well set references, enter this to C1 and fill down: =SUMIF($A$1:A1,A1,$B$1:B1).

2

You can do this with an array formula. For a fixed range, the formula in C2 would be
=IF(MAX(IFERROR(SEARCH($A$2:$A$5,$B2),0))>0,"yes","no")
Be sure to enter this as an array formula using Ctrl+Shift+Enter. You'll know it worked when you get curly brackets { } on either end of the formula.
If you want the range to be dynamic, one good way is to define ...

2

Calculate once, copy the range of formulas, then Paste Special (right-click target, then S) the Values only back into the range. This replaces the formulas with their results and will prevent the random passwords from being recalculated.

2

This may be new since the question, but (LibreOffice 4.2.7.2 on Linux), Edit->Fill->Series. Choose Date as the series type and Weekday as the Time unit. I found that, after the fill, I had to select the filled cells and Format->Cells to select date formatting. (The original fill was a series of numbers.)

2

Combine the SUBSTITUTE and TEXT functions. You may have to change the "." based on your regional settings. To convert the value shown in A1 the formula is:
=SUBSTITUTE(TEXT(A1,"0000000.00"),".","")
Note that it will only convert numeric values. Any text or errors are simply returned unchanged.
If you put in very large values, the result will be longer. ...

2

If you multiply by 100 that will get rid of the decimal point, then you can just use TEXT function to give you the leading zeroes, e.g. with your salary amount in A1
=TEXT(A1*100,"000000000")

2

Put your 50 character string in cell A2 and put your field widths in B1,C1,....
Then in B2 enter:
=LEFT(A2,B1)
and in C2 enter:
=MID($A$2,SUM($B$1:B1)+1,C1)
and copy C2 across. Here is an example:
Just be sure that top row of field widths add up to 50
While this solution does use individual string functions, it is completely driven by the field ...

2

Put these two subs in a standard module and the run MakeButton
Sub MakeButton()
ActiveSheet.Shapes.AddShape(msoShapeOval, 228#, 557.25, 126.1, 126.1).Select
With Selection
.Characters.Text = "1 ADDER"
.ShapeRange.Fill.Visible = msoTrue
.ShapeRange.Fill.ForeColor.SchemeColor = 3
.ShapeRange.Line.Visible = msoTrue
...

1

I would recommend using COUNTIFS, which allows to use multiple ranges and multiple conditions without making the formula statement too complicated.
The general format is the below:
COUNTIFS( criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )
Which for your case be would like this:
...

1

We will just use formatted values:
Sub qwerty()
Dim i As Integer
Dim s As String
i = 2
Do Until Cells(i, 1).Value = ""
If (s = "") Then
s = Format(Cells(i, 1).Value, "0000")
Else
s = s & ", " & Format(Cells(i, 1).Value, "0000")
End If
i = i + 1
Loop
Cells(1, 2).Value = s
End Sub

1

This is what you are looking for
The formula for ease of copy-paste is added below
=SUMIF($B$4:$C$12,D4,$C$4:$C$12)
Note: If you are new to excel formulas and wondering about the $ signs in the formulas - read about relative and absolute cell addressing here

1

You start your formula in row 3 that's why it starts from 3rd result. Either start in first row or use ROW(ITS!3:56)-2.
Why do you need to use formula at all? I'd use pivot table (if you need to show cucumber in each row you can set "repeat item labels").

1

I have two solutions for you:
Convert your data to a table (insert - table) and you won't need to worry about formula auto fill when you add new data, so your users range will be really the range you use.
If the previous solution doesn't work: select a cell in a data column (not one with pre-filled formulas that extend forever) and press CTRL+ DOWN ...

1

It appears that Excel treats "true/TRUE" and "false/FALSE" as magic strings when they appear in formulas -- they are treated as functions: TRUE() and FALSE() instead of strings.
If you need to count true and false in a column, you will not get a count if you use "true" or "=true" as the criteria. There are some approaches you can use to count true and false ...

1

I'm using excel 2010 and just highlight the two columns that have the two sets of values I'm comparing, and then click the Conditional formatting dropdown on the home page of excel, choose the Highlight Cells rules, and then differences. It then prompts to higlight either differences or similarities and asks what colour highlight you want to use...

1

You can use SUMPRODUCT as long as you have the right syntax, like this
=SUMPRODUCT((AH6:AS36="A")*AG6:AG36)
in fact SUM also works but you would need to "array enter" if you use SUM, i.e.
=SUM((AH6:AS36="A")*AG6:AG36)
confirmed with CTRL+SHIFT+ENTER

1

First install the following event macro in the worksheet code area:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim A As Range
Set A = Range("A:A")
If Intersect(Target, A) Is Nothing Then Exit Sub
Cancel = True
Cells(Target.Row, 3).Interior.ColorIndex = 27
End Sub
If you double-click on any cell in ...

1

F3:F11 is a range, but you're entering it in a place where it's only appropriate to give a single value, so Excel tries to choose one value from the range, using the following rules:
If the range is in a single column (as this is) Excel chooses the cell from that column in the same row as the referring cell (or the error #VALUE! if the range doesn't ...

1

Give this a try:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 3).Value = Target.Value
Target.Clear
Application.EnableEvents = True
End Sub

1

I'd do it with pivot chart: put KRA to "axis fields" and total rating to values (use max function).
To facilitate updating of your chart convert your data range to data table first so you won't need to change source data range when you enter data.
Also you can add very simple code to your sheets module to update chart when data changes. (just change ...

1

The most difficult part of this exercise is determining what, in column A, constitutes a last name. In your example, it's either:
The first word, if there's a comma in the whole name
The second word
If that rule is true, then you can just do a formula like this:
=NOT(ISERROR(FIND(last_name, B1:B4)))
The formula to actually determine the last name is a ...

1

What kinds of controls are those? The easiest solution is to add Form Controls (as opposed to ActiveX Controls).
After that, right-click on them and click on "Format Control..." at the bottom of the context menu. In the window that opens, go to the "Control" tab - if you're not there already - and select a range for the "Cell link:" field.
Now, any ...

1

Add a new sheet named roomtype to the workbook and in column A enter the room numbers. In column B enter the 1 or 0 for each room. Then go back to the sheet where you want the formula.
=VLOOKUP(A3,roomtype!A:B,2,0)
where A3 is the cell that has the entered room number.
The first parameter to VLOOKUP is a value specifying what value to look up.
The ...

Only top voted, non community-wiki answers of a minimum length are eligible