Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If cell not null use that value
First off thanks to everyone for their help over the past 2 weeks, I am
almost at the end of the development of the guide I have another help request I am using this; =IF(ISERROR(INDEX('Value Unit Prices'!C22:N41,MATCH(C29,'Value Unit Prices'!A22:A41,0),MATCH(Pricelevel,'Value Unit Prices'!D21:N21,0))*P29),"",(INDEX('Value Unit Prices'!C22:N41,MATCH(C29,'Value Unit Prices'!A22:A41,0),MATCH(Pricelevel,'Value Unit Prices'!D21:N21,0))*P29)) Which works perfectly however PriceLevel can be changed so I have a cell called leveloveride I want include in this formula, basically if leveloveride is not null that the formula is to use the level in leveloveride in its equation, if leveloveride is 'null' the formula is to use Pricelevel in its equation. I need it setup this way because Pricelevel is pulled directly from the DB I am using so I can have them users manually changing that cell. Any help would be greatly appreciated Thanks in advance -- Neall |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If cell not null use that value
Neall wrote...
.... I am using this; =IF(ISERROR(INDEX('Value Unit Prices'!C22:N41, MATCH(C29,'Value Unit Prices'!A22:A41,0), MATCH(Pricelevel,'Value Unit Prices'!D21:N21,0))*P29),"", (INDEX('Value Unit Prices'!C22:N41, MATCH(C29,'Value Unit Prices'!A22:A41,0), MATCH(Pricelevel,'Value Unit Prices'!D21:N21,0))*P29)) This may work, but it's unlikely you either need to check for error values in P29 or would benefit from trapping such errors of they exist. I'd guess you only need to trap C29 not in 'Value Unit Prices'! A22:A41 or PriceLevel not in 'Value Unit Prices'!D21:N21. If so, it'd be more robust to use =IF(COUNT(MATCH(C29,'Value Unit Prices'!A22:A41,0), MATCH(Pricelevel,'Value Unit Prices'!D21:N21,0))=2, INDEX('Value Unit Prices'!C22:N41, MATCH(C29,'Value Unit Prices'!A22:A41,0), MATCH(Pricelevel,'Value Unit Prices'!D21:N21,0))*P29,"") Which works perfectly however PriceLevel can be changed so I have a cell called leveloveride I want *include in this formula, basically if leveloveride is not null that the formula is to use the level in leveloveride in its equation, if leveloveride is 'null' the formula is to use Pricelevel in its equation. Change current references to PriceLevel to IF(ISBLANK (LevelOveride),PriceLevel,LevelOveride). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Failed to save table attributes of (null) into (null). | Excel Discussion (Misc queries) | |||
Sum a NULL cell | Excel Worksheet Functions | |||
If values of Cell are Null........... | New Users to Excel | |||
IF function and null cell value | Excel Worksheet Functions | |||
cell value based on null/not null in another cell | Excel Worksheet Functions |