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:
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?
Bookmarks