Excel – Populate empty table with data from oter table based on dropdown list selection

microsoft-excel-2007

I have 3 tables, Tab1, Tab2 and Tab3 containing data and an empty table Tab
The user can select in a drop-down list, option1, option2 and option3.

On the selection from user the empty table Tab will be populated with the corresponding table.

For example, when the user selects option2, the Tab is populated with the data from Tab2; all the tables have the same size.

I could manage to use INDIRECT to add value for one cell but not to the complete table.
Have you any ideas, any easy formula for Excel 2007. I am not using VBA.

Best Answer

I would make your drop down values the name of your tables if possible. And assign a named range to the cell that contains the resulting dropdown value,such as OptionPicked. In your empty range that is the same dimmensions as the three tables put this formula in every cell.

INDEX(INDIRECT(OptionPicked &"[#All]"),ROW()-ROW($A$1),COLUMN()-COLUMN($A$1))

This will work if the empty table Tab is not an actual excel table as formulas are not allowed in the headers of excel tables.

If you don't want to use the table names as the drop down choices you could build two lists that hold your TableNames and your DropDownChoices and have your drop down box refer to the list =DropDownChoices and the cell that contains that dropdown would still be named OptionPicked. Then in every cell in the Tab "table" enter this formula

=INDEX(INDIRECT(INDEX(TableNames,MATCH(OptionPicked,DropDownChoices,0)) &"[#All]"),ROW()-ROW($A$1),COLUMN()-COLUMN($A$1))

enter image description here