## Hot answers tagged microsoft-excel

102

As a macro-less alternative, this won't change the value of the cell, but it will change the display of the cell. Right click on the cell in question, then click "Format Cells." In the number tab, click on Custom, then enter the following custom number format
"Joe";"Joe";"Joe";"Joe"
Explanation: Excel number formats have four parts, separated by ...

39

IFERROR function
There is a "special" IF test designed just to handle errors:
=IFERROR( (D11-C11)/D11, "")
This gives you the calculated value of (D11-C11)/D11 unless the result is an error, in which case it returns a blank.
Explanation
The "if error" value, the last parameter, can be anything; it isn't limited to the empty double-quotes. IFERROR ...

9

Within an IF statement is a logical check (the first part).
IF(logical_test, value_if_true, [value_if_false])
To avoid errors caused while your production and/or the goal data is blank, use the OR with the ISBLANK function within the logical check.
=IF(OR(ISBLANK(C11),ISBLANK(D11)), "", (D11-C11)/D11)
This checks if the either cell referenced is ...

8

Put the following event macro in the worksheet code area:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("A1").Value = "Joe"
Application.EnableEvents = True
End Sub

4

There is a VBA function which will determine the type of variable being dealt with: VarType().
The VarType of a string is vbString (8); the VarType of a range is vbArray+vbVariant (8204). You need to test each element of your passed array and deal with it accordingly. One way to do this which is easy to follow is with Select Case:
Function Separate(sp As ...

3

This can be accomplished through a combination of the INDEX and MATCH functions. For example:
=INDEX($A$1:$D$6, MATCH($G$2, $A$1:$A$6, 0), MATCH($G$1, $A$1:$D$1, 0))
The MATCH function determines the target row and column, and the INDEX function looks up the cell in that row and column. The table range (A1:D6) and input cell addresses (G1 and G2)can be ...

3

User tohuwawohu posted an answer that led me directly to mine. I don't think tohuwawohu quite understood what I wanted, perhaps through my fault, but his macro suggestion helped me to understand. I just made a macro, which is now loaded into all sheets and which defined a function that I can call with the same syntax as a built-in function.
But, for one ...

3

You've already answered yourself with a macro, but here is a non-VBA solution. It's an array formula, and must be confirmed with ctrl+shift+enter:
=(SUM(IFERROR(1/COUNTIF(A1:A3,A1:A3),0))=1)
This formula counts the number of unique values in your range, while ignoring blank cells. If the number of unique values is 1, then every value is the same and the ...

3

For hours you can use
=SUM(B2:B9)+INT(SUM(C2:C9)/60)
And minutes you can use
=MOD(SUM(C2:C9),60)

2

PDFMaker is a program/Office add-on from Adobe, not part of Office.
If, instead, you use Office's internal Save As.. and pick PDF, it gives you an option as to whether you'd like it to open after saving (aka 'Publishing') or not.
Perhaps PDFMaker has a similar option but I can't find documentation of it, and I'm assuming you already checked for that ...

2

For a LO Calc solution, i see two ways:
put the function that yields the result for A into a separate, protected sheet. This way, it's quite unlikely that the user accidentally tries to edit the cell holding your function. It has some other advantages:
no need for macro coding,
ability to copy the "function" sheet into other ods files,
simple to edit.
...

2

You have a list of A's and a list of B's. You have some (A,B) combinations that are false, the rest are true.
Create a new list of the forbidden combinations, then add another column that concatenates the combinations that you don't allow like "MondayCleaning", "WednesdayVacuuming" etc. To concatenate strings use this formula:
=A1&B1
To make adding ...

2

Check if each column-pair exactly equal (case-sensitive) or contain a blank.
=OR(EXACT(A2,B2),ISBLANK(A2),ISBLANK(B2))
=OR(EXACT(A2,C2),ISBLANK(A2),ISBLANK(C2))
=OR(EXACT(B2,C2),ISBLANK(B2),ISBLANK(C2))
=AND(D2:F2)
Example:
A B C AB AC BC AND
1 1 1 TRUE TRUE TRUE TRUE
1 1 TRUE TRUE TRUE TRUE
A ...

2

Split the date up into year/month/day and add 3 months:
=DATE(YEAR(A1),MONTH(A1)+3,DAY(A1))
assuming A1 holds your start date.

2

Replace:
cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value, Len(cell.Value) - 1)
With:
comma = InStr(cell.Value, ",")
If comma <> 0 Then
cell.Value = Left(cell.Value, comma) & UCase(Mid(cell.Value, comma + 1, 1)) & Right(cell.Value, Len(cell.Value) - comma - 1)
End If
It will convert to uppercase the letter after the ...

2

It is probably not the cleanest solution, but it works:
=RIGHT(A1, LEN(A1)-FIND("tagid=",A1)-LEN("tagid=")+1)

2

This can indeed be accomplished by using SUMIF. SUMIF allows you to specify a criteria range and a sum range separately. In the following example, the criteria range A:A (entire column A) will be matched for the criteria (D1) and the values in the sum range B:B (entire column B) will be summed if the criteria is satisfied.
=SUMIF(A:A, D1,B:B )
Note that ...

2

If your example is representative, and a hyphen will be found only within this number pattern, you could use:
=MID(A1,FIND("-",A1)-2,6)
This just finds the position of the hyphen in the column A string, and then takes six characters, starting two characters before the hyphen.

2

If you declare an argument as Variant in the function header, you can always determine what the caller REALLY passes you as follows:
Sub MAIN()
x = WhatIsIt(Range("A1"))
y = WhatIsIt("what ever")
End Sub
Public Function WhatIsIt(v As Variant) As String
WhatIsIt = ""
MsgBox TypeName(v)
End Function

1

In B1 enter:
=IF(LEN(SUBSTITUTE(A1,"cats",""))=LEN(A1),"","CAT Lover")
and copy down:

1

The formula you are looking for is:
=INDIRECT(A4)
or
=INDIRECT("F"&A2)

1

This sub deletes the entire row(s) of a sheet when a value defined by the variable thestring is in the column defined by the variable thecolumn
Sub deleterows()
Dim a As Application
Dim wkb As Workbook
Dim wks As Worksheet
Dim DataRange As Range
Set a = Application
Set wkb = ThisWorkbook
Set wks = wkb.ActiveSheet
...

1

The dot series needs to be an XY/ScatterChart type. Then, just adjust your dot series X values to match your series position.
In your example, assuming that your XY chart is on the primary axis, you should set:
First blue dot X value approx.
0.85, then add 1 for each additional dot
First yellow dot X value 1.15, then add 1 for each additional dot

1

An important difference between inserting a blank column and pasting into it, vs. "insert cut cells" is that the later will shift your other columns to the right, but NOT their column width formatting. So if you 'insert cut cells,' all the columns to the right will have the widths of the their former columns one to the right and you will have to readjust ...

1

You don't need code to accomplish this. When you click to Protect the worksheet there will be a drop down with a list of items you can allow. Click format columns and rows.

1

Why don't you just use =REPLACE(A2,1,12,"") this replaces characters 1-12 with "" (nothing) and so just displays the lookup (A2) from the 13th character, removing "Application:"

1

In case anyone else runs into this, I was able to synthesize a solution from the updates and other comments above. When you paste the data from Excel into Word, choose Paste > Paste Special..., then choose the Paste Link radio button, and Unformatted Unicode Text. Looks like this in Office 2016:
If your content actually has some formatting, one of the ...

1

Just combine Concatenate() with if() in an array function:
=arrayformula(concatenate(if(not(isempty(a2:f2)),a2:f2&", ","")))
This function will leave a comma at the end of your list, so if you need to avoid that and you know exactly where the end of the list is (in your example, column f) just handle that one separately:
...

1

In column V1 use Ctrl + ↓ to reach the bottom of the column.
→ across to column W, then Ctrl + Shift + ↑ to select the column to the top of the cell.

1

I like to use the MID function because I can then modify the last parameter (length) in case my strings change in the future.
=MID(A1,SEARCH("=",A1)+1,999)
It looks for the equal sign, then takes 999 characters to the right of that.
In the future, if you add another parameter (e.g. ?tagid=1234567&userid=5555), you can simply replace the "999" for a ...

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