## Hot answers tagged worksheet-function

3

Table1[#All] includes the headers, Table1[Code 2] and Table1[Code max] do not. Instead of Table1[#All] just use Table1.

2

You can use an IF and an OR to make sure either WEEKLY or BILLS are populated:
=(IF(OR(B3>0,C3>0),D2+B3-C3,""))
This checked if B3 or C3 are over zero. If one of them are, it'll calculate, otherwise it'll show an empty string.

2

Assuming your data is in A1:M13, this formula uses a value input into cell O2 and returns the column and row header corresponding to that value. For example, if you input 63, the formula returns LI.
=INDEX(A1:M1,SUM(IF(A1:M13=O2,COLUMN(A1:M13),"")))&INDEX(A1:A13,SUM(IF(A1:M13=O2,ROW(A1:M13),"")))
This is an array formula, and must be confirmed with ...

2

You can use the IFERROR function to handle what to do with #N/A results.
For example
=IFERROR(YourLookup,10.5)
This would return the result of the lookup if it's successful, or 10.5 if it fails to find a result.

2

As mentioned by Máté Juhász, =VLOOKUP can be used with some data shifting. From the KB on vlookup
Syntax - VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
range_lookup (optional)
A logical value that specifies whether you want VLOOKUP to find an
exact match or an approximate match:
TRUE assumes the first column in the ...

1

The problem you're having is because you are formatting your output values as strings instead of numbers (You are using " around the value).
Try changing your formula to:
=IF(G15=1,5.06,IF(G15=2,12.92,IF(G15=3,20.78,0)))
this should treat the outputs as numbers and they will be correctly added up.

1

In your example, dragging the formula from column F to column G will change the formula to "('Time sheets'!H10-'Time sheets'!G10)*24". You are trying to increment the columns in the formula by two, which can't be done by simply dragging, only by dragging over two columns and then deleting the intermediate column, or by VBA programming.

1

The overriding issue is how to access the formula string of another cell from the worksheet. There is no existing worksheet function that can do this. So, you're going to have to do one of two things to start: 1) use find-and-replace to change the content of the cells so that they no longer hold formulas but hold strings instead, or 2) use VBA to access the ...

1

The equivalent to grep in Excel is AutoFilter. You can use it to view subsets of data based on criteria.
However, to use it your data would require re-organization. The criteria must appear in columns rather than rows. So you would a column for -6, -7, -8, -9, -10, etc.

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