Thread: Filtered Data Validation Lists

I need to find a way to list valid 2nd column values from a 2 column range, by filtering using the first column in the the list. for example:

A Apple
A Aardvark
A Animal
B Banana
B Baboon
B Basketball
B Balloon
B Barium
C Carrot
C Cat
C Car
C Charity

On another sheet, on each row, a validated cell lists values from the second column whose first column matches the value from a cell in the same row

So a cell value of C would list only carrot, cat, car and charity in the drop-down.

I would have thought a formula like:

=IF(Sheet1!A1=Sheet2!A:A, Sheet2!B:B, "")

might work, but the data validation window doesn't like it. (and won't let me do array formula - ctrl+shift+enter or {...})

I can't re-arrange the look-up data and i'd like to avoid VBA if possible. (it will be added to, so will need to be dynamic and in reality there might be more columns)

Is there a way to automatically generate range names in this layout, then use indirect for the validation?

Much appreciated

Yes, define a named range called A covering Apple, Aardvark and Animal, a range called B covering Banana to Barium, and a range C covering Carrot to Charity. Then if Sheet1!A1 contains one of those letters, your data validation source list for B1 (say) should contain the formula =INDIRECT(Sheet1!$A$!).

Hope this helps.

Pete

I cant do this manually, i have hundreds of entries in reality, that's why i asked if there was a way to automatically generate range names for this layout.
Also, they may get shuffled/sorted, so accounting for that too would be useful.

check out this site there may be somthing in here that will help
https://sites.google.com/a/madrocket...ssistant/files

Thanks that's great. AutoCompleteDataValidation(SortOf)2.xls is closest to what i want to do, although I only want a single-stage dropdown, but I will reference another cell value for the filter.

So my revised formula is:

=INDEX(PartList, MATCH(A1&"-*", PartList)) : INDEX(PartList, MATCH(A1&"-zzz", PartList))

I think I understand what it's supposed to do, It looks for the first entry in the list using the A1&"-*" bit, then the last entry using the Approx. match A1&"-zzz" bit.
This means the list has to be sorted alphabetically, but that's fine.

Where PartList is a list of data in the following format:

A-Apple
A-Aardvark
A-Animal
B-Banana
B-Baboon
B-Basketball
B-Balloon
B-Barium
C-Carrot
C-Cat
C-Car
C-Charity

Made with '=A1&"-"&B1 formula in column next to existing data.

(I realise for this example it's a bit redundant, but this isn't my real data)

However, I get this error (Which prevents it from passing):

/!\ You may not use reference operators (such as unions, intersections, and ranges) or array constants for Data Validation criteria
[OK]

Does anyone know if I've made a mistake or how to get around this?

Sorry to bump, but i really need to know and this seems like it might work.

Hi

Selection criteria in Sheet1:A1 (C)
Data in Sheet2!A:A
Sheet2!B1: =IF(LEFT(A1,1)=Sheet1!$A$1,ROW(),"") - copy down
Sheet2!D1: =IF(ROW()>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW())))

Then create a defined name with the Refers to: =OFFSET(Sheet2!$D$1,0,0,COUNT(Sheet2!$B:$B),1)

See how that goes.

rylo

Pl see the attached fle.
Using same sheet and helper cells validation done in Sheet1.
Using code for worksheet change event validation is done in Sheet2.

You can use which is convenient for you.

Okay, it's not working as perfectly as I initially thought.

I think excel has a bug

when the 'rows' (row offset) value goes above 40 the alignment starts to mess up.

Can anyone else verify this? Does anyone know how to inform Microsoft this needs fixing if it's confirmed?

I've tried to export my document to openoffice format and to import the xlsx to google docs and openoffice, but nothing has worked.

Is anyone else able to confirm this as a bug?