Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Concatenate operator to overcome 7 nested IF's | Excel Worksheet Functions | |||
SUM returns #VALUE! error | Excel Worksheet Functions | |||
Why are my nested sub-totals are displaying incorrectly? | Excel Discussion (Misc queries) | |||
Problem with GEOMEAN - returns #NUM error | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |