Greetings, hive! Here's one for you.
Parameters thus far:
- Cell C5 has data validation applied for a drop-down list which points to a named table
- Every list item in "Ensembles" also serves as the name for one of 24 other named tables, for further INDIRECT, as described below
My aim:
#1 - When an item is chosen in C5, corresponding named table is automatically fed to cells A16:C61 as required. (Each table has 3 columns; varying number of rows.)
#2 - Some tables may have blank rows; once data is populated to area described above, would like to be able to clean up the blank rows via cut/paste cells.
#3 - After all that, I'd like to preserve the auto-feed functionality. (i.e. Select a different item in C5, and new table displays; select the same item in C5, and table is re-generated with any blank rows that may exist.)
What I've got in place right now:
Cells A16:A61
Cells B16:B61
Cells C16:C61
This is close, but of course it doesn't allow for point #2 above. Hoping there's a simple tweak that will fix things up as needed.
Thanks for any time and help you're willing to spare!
Shawn
Parameters thus far:
- Cell C5 has data validation applied for a drop-down list which points to a named table
Code:
=INDIRECT("Ensembles")
- Every list item in "Ensembles" also serves as the name for one of 24 other named tables, for further INDIRECT, as described below
My aim:
#1 - When an item is chosen in C5, corresponding named table is automatically fed to cells A16:C61 as required. (Each table has 3 columns; varying number of rows.)
#2 - Some tables may have blank rows; once data is populated to area described above, would like to be able to clean up the blank rows via cut/paste cells.
#3 - After all that, I'd like to preserve the auto-feed functionality. (i.e. Select a different item in C5, and new table displays; select the same item in C5, and table is re-generated with any blank rows that may exist.)
What I've got in place right now:
Cells A16:A61
Code:
=IFERROR(INDEX(INDIRECT($C$5),ROW()-15,1),"")
Cells B16:B61
Code:
=IFERROR(INDEX(INDIRECT($C$5),ROW()-15,2),"")
Cells C16:C61
Code:
=IFERROR(INDEX(INDIRECT($C$5),ROW()-15,3),"")
This is close, but of course it doesn't allow for point #2 above. Hoping there's a simple tweak that will fix things up as needed.
Thanks for any time and help you're willing to spare!
Shawn