Please help w/ nested IF that returns a #N/A.

S

smoore

I have the following nested IF function in a worksheet that gets its
info from a cell on another sheet. This cell "H2" is linked to a
combobox on sheet 1.

=IF(VLOOKUP(Companies!H2,Companies!A2:F200,3,0)="",VLOOKUP(Companies!H2,Companies!A2:F200,4,0)&","&"
"&VLOOKUP(Companies!H2,Companies!A2:F200,5,0)&""&VLOOKUP(Companies!H2,Companies!A2:F200,6,0),VLOOKUP(Companies!H2,Companies!A2:F200,3,0))

This was all working as intended until I decided that I wanted the
combobox to be blank when the sheet is first opened. I tied an
auto_open function to it and now when the sheet is opened the combobox
is empty, like I want it, but this cell shows a #N/A. I've tried to
work an ISNA into this but haven't stumbled to the right one yet. Do
any of you gurus out there have an idea.?

Thanks.
 
A

Ardus Petus

Just test if Companies!H2 is blank:

=IF(Companies!H2="","",IF(VLOOKUP(Companies!H2,Companies!A2:F200,3,0)="",VLO
OKUP(Companies!H2,Companies!A2:F200,4,0)&","&""&VLOOKUP(Companies!H2,Compani
es!A2:F200,5,0)&""&VLOOKUP(Companies!H2,Companies!A2:F200,6,0),VLOOKUP(Compa
nies!H2,Companies!A2:F200,3,0)))

HTH
 
S

smoore

Ardus, just perfect. Can't thank you enough. I'm amazed at how easily
you folks decifer some of these function and come up with the correct
fix. Thanks again.

Scotty Moore
 
Top