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

[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.

 

[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

 

[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