Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with N/A in formulas
Afternoon everyone
I have 2 formulas that I need a little bit of help on. Both are working fine but I want the cell to be blank if the result is N/A. Just not sure how I add it in, 1st Formula =VLOOKUP(A2,'NEW Sales booked Jan-Mar'!$1:$65536,1,FALSE) 2nd Formula =NETWORKDAYS(H2,U2) But I need the 2nd formula seems a little trickier H2 will always have a date in it. But U2 wont if the VLOOKUP returns a BLANK result So I need the 2nd formula to read if U2 doesn't have a date in it put blank, otherwise run the formula. Thanks in advance for your help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with N/A in formulas
Hi Mark,
In Excel 2003 use = IF(ISNA(VLOOKUP(A2,'NEW Sales booked Jan-Mar'!$1:$65536,1,FALSE)),"",VLOOKUP(A2,'NEW Sales booked Jan-Mar'!$1:$65536,1,FALSE)) In Excel 2007 = IFERROR(VLOOKUP(A2,'NEW Sales booked Jan-Mar'!$1:$65536,1,FALSE),"") Similar for the 2nd formula. Hope this helps. With kind regards, JP "Mark D" wrote in message ... Afternoon everyone I have 2 formulas that I need a little bit of help on. Both are working fine but I want the cell to be blank if the result is N/A. Just not sure how I add it in, 1st Formula =VLOOKUP(A2,'NEW Sales booked Jan-Mar'!$1:$65536,1,FALSE) 2nd Formula =NETWORKDAYS(H2,U2) But I need the 2nd formula seems a little trickier H2 will always have a date in it. But U2 wont if the VLOOKUP returns a BLANK result So I need the 2nd formula to read if U2 doesn't have a date in it put blank, otherwise run the formula. Thanks in advance for your help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with N/A in formulas
Excellent, thank you
"JP Ronse" wrote: Hi Mark, In Excel 2003 use = IF(ISNA(VLOOKUP(A2,'NEW Sales booked Jan-Mar'!$1:$65536,1,FALSE)),"",VLOOKUP(A2,'NEW Sales booked Jan-Mar'!$1:$65536,1,FALSE)) In Excel 2007 = IFERROR(VLOOKUP(A2,'NEW Sales booked Jan-Mar'!$1:$65536,1,FALSE),"") Similar for the 2nd formula. Hope this helps. With kind regards, JP "Mark D" wrote in message ... Afternoon everyone I have 2 formulas that I need a little bit of help on. Both are working fine but I want the cell to be blank if the result is N/A. Just not sure how I add it in, 1st Formula =VLOOKUP(A2,'NEW Sales booked Jan-Mar'!$1:$65536,1,FALSE) 2nd Formula =NETWORKDAYS(H2,U2) But I need the 2nd formula seems a little trickier H2 will always have a date in it. But U2 wont if the VLOOKUP returns a BLANK result So I need the 2nd formula to read if U2 doesn't have a date in it put blank, otherwise run the formula. Thanks in advance for your help . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with N/A in formulas
You are welcome, thanks for the feedback.
"Mark D" wrote in message ... Excellent, thank you "JP Ronse" wrote: Hi Mark, In Excel 2003 use = IF(ISNA(VLOOKUP(A2,'NEW Sales booked Jan-Mar'!$1:$65536,1,FALSE)),"",VLOOKUP(A2,'NEW Sales booked Jan-Mar'!$1:$65536,1,FALSE)) In Excel 2007 = IFERROR(VLOOKUP(A2,'NEW Sales booked Jan-Mar'!$1:$65536,1,FALSE),"") Similar for the 2nd formula. Hope this helps. With kind regards, JP "Mark D" wrote in message ... Afternoon everyone I have 2 formulas that I need a little bit of help on. Both are working fine but I want the cell to be blank if the result is N/A. Just not sure how I add it in, 1st Formula =VLOOKUP(A2,'NEW Sales booked Jan-Mar'!$1:$65536,1,FALSE) 2nd Formula =NETWORKDAYS(H2,U2) But I need the 2nd formula seems a little trickier H2 will always have a date in it. But U2 wont if the VLOOKUP returns a BLANK result So I need the 2nd formula to read if U2 doesn't have a date in it put blank, otherwise run the formula. Thanks in advance for your help . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
E2007 formulas display as formulas, not results | Excel Worksheet Functions | |||
Counting # of Formulas in a column with formulas and entered data | Excel Worksheet Functions | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) |