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

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)&""&V LOOKUP(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.

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

Just test if Companies!H2 is blank:

=IF(Companies!H2="","",IF(VLOOKUP(Companies!H2,Com panies!A2:F200,3,0)="",VLO
OKUP(Companies!H2,Companies!A2:F200,4,0)&","&""&VL OOKUP(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
--
AP


"smoore" a écrit dans le message de
ups.com...
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,Comp
anies!A2:F200,4,0)&","&"

"&VLOOKUP(Companies!H2,Companies!A2:F200,5,0)&""&V LOOKUP(Companies!H2,Compan
ies!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.

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

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