We help IT Professionals succeed at work.

If statement help needed

Mark Wood
Mark Wood used Ask the Experts™
on
I have a spreadsheet that currently has 2 sheets on it and i am using data validation with named ranges to pull a list of names from sheet 1 and put them in a drop down list on sheet 2. =IF(VLAN="SCARS",SystemName)

It returns an error, "The Source currently evaluates to an error", but it worked fine when i only had 1 system name in the list.

example attached, please help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark WoodNetwork Administrator

Author

Commented:
sorry ... here is the file
Compliance-Status-Report-SCARS-Temp.xlsx
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
You are abusing the functionality of the data validation dropdown with your formula. I'm surprised it worked in cells B6 and B7.

How would you like the dropdown to work? Since VLAN has three different values, how would you like the dropdown to work in Totals worksheet cells B6, B7 and B14? Don't give me a formula (that's my job): tell me in words what you want Excel to do.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
In Vlookup Function, the lookup value should be in the first column in the table_array which is not case in your formula.

You may try one of these formulas in C2...

=IFERROR(VLOOKUP(@SystemLookup,Table1[[Computer Name]:[IP Address]],3,FALSE),"")

Open in new window

OR

=IFERROR(VLOOKUP(@SystemLookup,Table1[[Computer Name]:[IP Address]],3,FALSE),"")

Open in new window


Same way you can tweak the other formulas.
Mark WoodNetwork Administrator

Author

Commented:
I need it to pull a list of computer names based on the VLAN designation. (SCARS, IG, etc. There will be 5 different designations when i am done.

So basically in the top section of sheet 2 it will only show a list of names where VLAN = SCARS and in the second section it will only show a list of names where VLAN = IG and so on.
Mark WoodNetwork Administrator

Author

Commented:
Subodh,

That works great for displaying the IP address that corresponds to the SystemLookup but not work for the OS or Function fields
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I changed your file to use two new named ranges: SCARSnames and IGnames. These refer to values in Setup worksheet columns J and K, and are used in the data validation dropdown in worksheet Totals.
Compliance-Status-Report-SCARS-Temp.xlsx
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Try this...

For OS:
=IFERROR(INDEX(Table1[OS],MATCH(@SystemLookup,Table1[Computer Name],0)),"")

Open in new window


For Function:
=IFERROR(INDEX(Table1[OS],MATCH(@SystemLookup,Table1[Computer Name],0)),"")

Open in new window

Mark WoodNetwork Administrator

Author

Commented:
Thanks so much for the help.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Mark!