Filtering a Data Validation list

Common problems – different language?
Let's talk in English.

Filtering a Data Validation list

Poslaťod Palo-admin » 18.09.2009, 08:44

Dirk Harris posted
I have a cell (on Sheet1) that contains a list populated from an array on another worksheet (='Sheet 2'!G2:BA2); the referenced array row contains some data that I do not want to include in the list choices. Is there a way to either a) filter the results, b) filter the results for non-duplicate data, and/or c) limit the results to non-blank cells? The referenced array is dynamic and can not be deleted.

Simplest way is via macro.
But when you prefer "non macro" solution it is possible by combination
of functions OFFSET, MATCH and COUNTIF.

See attached sample: ... dation.xls
NOTE: Lists source should by sorted.

Answer for 2-nd part of your question: "non dupli, sorted ..."

All can be done, if Pivot table result is used as a source list for Data Validation.
Example: ... tTable.xls

1) Unique list of cities - Pivot table created from source "CityList"
2) Range of unique city names in Pivot named as "CityUnique"
3) Range "CityUnique" used as source for Data Validation

Note: Range of cells for "CityUnique" can be dynamically changed always, when Pivot table is refreshed
e.g. when specific country selected

see (ALT+F11 ) functions Worksheet_Change, NamedRange_ReDimension
Administrátor stránky
Príspevky: 473
Registrovaný: 18.07.2008, 16:43
Udelené poďakovania: 6 krát
Prijaté poďakovania: 63 krát

Späť na Solving MS Office problems in English

Kto je on-line

Užívatelia prezerajúci fórum: Žiadny registrovaný užívateľ nie je prítomný a 1 hosť