Data validation filtered list based on a criteria

**fredotom** · 04-24-2015, 09:46 AM

Hi guys,
I'm struggling with a little case.
On sheet 1 I have a list of attributes based on a code:
24-04-2015 15-36-28.jpg

On my second sheets I have a column with type and a column for attribute.
24-04-2015 15-37-53.jpg

What i want to do sounds simple but I haven't found a solution.
If name/type is "cable" I want a dropdown list for "att" allowing only code1 values.
If name/type is "htrc" I want a dropdown list for "att" allowing only code2 values.

What I've tried is:
- defining a named range called "codes" for $A:$A on sheet1
- data validation for B2 on sheet2 and used formula: =IF($A2="cable";INDIRECT(codes="code1");INDIRECT(codes="code2"))

When I'm entering that I'm getting a message saying "The Source currently evaluates to an error. Do you want to continue?" and the dropdown is blank...

Anyone can help?
I'm also attaching the file.

testbook.xlsx

I hope someone can help me...

**JBeaucaire** · 04-24-2015, 10:56 AM

1) To make this dynamic, you need a second named range on your Sheet1. In column C:D create a lookup table where you can assign names (column C) to codes (column D)

Please Login or Register  to view this content.

Now highlight C:D and give those two columns a named range value of "KeyNames". Now you can add as many options to this list as you wish.

2) The data validation formula in Sheet2 B2 is now:

=OFFSET(codes, MATCH(VLOOKUP(A2, KeyNames, 2, 0), codes, 0)-1, 1, COUNTIF(codes, VLOOKUP(A2, KeyNames, 2, 0)), 1)

**DBY** · 04-24-2015, 11:01 AM

Hello
Highlight the code1 values and Name them 'cable', likewise, highlight the code2 values and Name them 'htrc'. Then for the Data Validation List source, use the function:

Please Login or Register  to view this content.

*Just noticed Jerry's post after I posted. If you have many codes it's the way to go.

Hope this helps.
DBY

Data validation filtered list based on a criteria

LinkBack

Thread Tools

Rate This Thread

Display

Data validation filtered list based on a criteria

Re: Data validation filtered list based on a criteria

Re: Data validation filtered list based on a criteria

Thread Information

Users Browsing this Thread

Similar Threads

Data Validation List based on criteria

[SOLVED] Create Dynamic Data Validation List based upon Criteria

Dynamic Data Validation List Based On Criteria In Another Column

Filtered Validation List based on other variables

[SOLVED] data validation list should have opt. to select based on criteria

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions