[Solved] Double Dynamic Dropdowns with Data Validation

[NahtanoJ88]

In the example I attached I have two sheets Tracker which is the main sheet someone would be working on and Array which holds a list of data to be used for validated drop down boxes.

Tracker Column A - Dynamic List Validated using Array Column A
Tracker Column B - Dynamic list created from corresponding Row in Array
Array Rows have a different numbers of cells


What I've been attempting to accomplish is to populate a drop down box in Column B of Tracker based on what the user selected from a dynamic list in Column A on Tracker by Using the table in Array which holds multiple entries based off of Column A of Tracker. Neither

I've been using Column B on Array as a index key for different formulas I've tried in addition to having set a range.

My biggest challenge with this so far is keeping everything dynamic since I'm trying to avoid hard coding any values and I can't predict ahead of time out many items may end up in each row and I would like to avoid having a needless amount of blank choices cluttering the list. So far I've tried using vlookup with different combinations of offset, indirect, and countif in order to build the data validation for Column B of Tracker.

I know this should be possible and I do believe I've done this before I just can't seem to wrap my head around how OpenOffice want's me to make this. Any help or insight anyone could give me would be greatly appreciated.

[gerard24]

Formula for "Source" of validity cell B2:

Code: Select all

INDEX($Array.$B$1:$Z$1000;MATCH(A2;$Array.$A$1:$A$100;0))

I would like to avoid having a needless amount of blank choices cluttering the list.

Check the box "Sort entries ascending".

[Villeroy]

http://forum.openoffice.org/en/forum/do ... php?id=333

[NahtanoJ88]

Thanks, You guys are amazing that was much simpler then the crazy nested formula mess I was trying to make it work.

[galaxy5]

I wonder, why Villeroy used 'Named expressions' instead of named range? Is there some sense in this decision or just an arbitrary choice?
Under 'named range' I mean 'Data -> Define range'.

[Villeroy]

The difference between "named ranges" and "named expressions" is an artificial one. I used named expressions "Countries" ($Sheet1.$A$1:$A$4) and "Cities"($Sheet1.$B$1:$IV$4). These are absolute references to cell ranges, therefore "named ranges". In the same document you find some named expressions which are not used in that document. They return some text (current sheet, sheet separator, URL of document).

Then there are "database ranges" (Data>Define...). These are absolute references to database-like lists which may be linked to a row set of some external database.

Finally there are range labels which virtually nobody uses.

[galaxy5]

I found awesome using of just two ranges for countries and cities as opposed to type a list of cities for each country!
What do you mean under 'range labels'?

[Villeroy]

What do you mean under 'range labels'?

menu:Insert>Names>Labels... treats the first row and/or the first column as pointers to the entire row/column

='Charlie' references the entire row/column labeled "Charlie"
='Charlie'!'Alpha' references the intersecting cell with row label and column label "Charlie" and "Alpha"