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

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
--
AP


"smoore" <smoore@ragsdalesteel.com> a écrit dans le message de
news:1145369016.345226.83150@z34g2000cwc.googlegroups.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)&""&VLOOKUP(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.
>

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