If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 . |
Thread Tools | |
Display Modes | |
|
|