Creating a table where each row is a drop-down table

[DaddyGryph]

I’m trying to create a 7-column x 10-row table where each ROW is a drop-down table made from a separate table.

Target table:

• The header is the same as for all the feeder tables, so no need to replicate those headers.
• 1st column is a list of TITLES; each Title corresponds with one feeder table.
• The 2nd column (LEVEL) will hold the drop-down cells for each feeder table; the drop-down values for all the cells of this column will be 1-10.
• For the rest each row, the values will display with their corresponding LEVEL value (1-10).
• I’d also like to be able to add rows (& possibly columns, but unlikely) to each feeder table.

[elmau]

Show us how you’ve tried.

[DaddyGryph]

I just realized that I can attach images here if not the spreadsheet file itself, so I’ve attached a screenshot of a Source table & Target table similar to what I’m using.

I tried to build just one drop-down row, figuring that if successful, I could expand the solution to subsequent rows. Using variations of the Data>Define Range and the Data>Validity>Cell Range functions linking one of the Source tables either returned all the values from that table in a single drop-down column or just returned the name I’d assigned to the Source table in Define Range.

I found another suggestion using the INDEX & MATCH functions that wasn’t clearly explained and seemed like overkill.

Finally, I tried using a Combo Box method similar to that available in MS Excel, but in LibreOffice, Combo Boxes must link to a separate database rather than just another table in the same file.

All the other solutions I found seemed even more complex. Seems like it should be a relatively straightforward solution, but I’m stuck

Thank you.

Target table:

[DaddyGryph]

Source table:

[DaddyGryph]

Next attempt - I created a drop-down table in cell B2 of the Target table from cells B2:B11 of the Source table then created separate =VLOOKUP(Lookup; Source Array; Index; 0) functions in each of cells C2:G2. It worked – row 2 of the Target table is now essentially a 1-row drop-down table of the “Title 1” Source table. Though I can use the same 1-10 table for all the drop-down cells, each of the other cells in the Target table (C2:G11) would require a unique VLOOKUP formula - a long and tedious process for this one Target table, and my spreadsheet has several of these tables on one page.

Still looking for a simpler method. Thank you.

[Wanderer]

@DaddyGryph

realized that I can attach images here if not the spreadsheet file itself

It is possible to append also the spreadsheet with the upload button, wich is much more useful for people trying to understand, what you’ve done.

[DaddyGryph]

Thank you! I missed that icon.

I’ve attached a sample file with 3 sheets - Source Tables, Target Tables, & Using VLOOKUP in each cell. On the third sheet is a small solution using the =VLOOKUP(Lookup; Source Array; Index; 0) function I tried earlier. As I said, it works, but it requires a unique function in EACH of cells C3:G3. That would be very time-intensive! I sense there’s a way to expand the function to include an entire row from the Source Table, turning each Source Table into a 1x6 drop-down Target Table. Or is there another (better?) function to achieve that goal? As you can see, I have several tables – that already exist – to do this with.

Thank you.
Stacked-dropdowns-tables.ods (15.5 KB)

[Ash733]

Instead of VLOOKUP, consider INDEX + MATCH. You can then reference the headings in each column to lookup the value rather than hard coding in the column number.

For example in C3 you would use:

=INDEX($J$2:$O$7,MATCH($B3,$J$2:$J$7,0),MATCH(C$2,$J$2:$O$2,0))

Stacked-dropdowns-tables Ash733 edited 20240710.ods (24.6 KB)

Using a mixture of relative and absolute ($) cell references along with the INDEX and MATCH functions the formula is the same for each cell.

[DaddyGryph]

Thank you very much - I’ll look that over! I just realized that last time I uploaded an older, incomplete version of the sample table that I’d built. This correct one gives a better sense of what I need to create in case it produces a better response. Again, on the 3rd sheet, there are multiple Source tables and a Target table with the first row as the same drop-down table as before. The 2nd two rows are just static examples of how they would look for the other two Source tables with certain Levels chosen. I have several larger Target tables like this that I need to create, each from several Source tables.

Thank you again and I’ll try the INDEX/MATCH formulas.

Stacked-dropdowns-tables2.ods (17.1 KB)