Forum Discussion
Data viewing on other excel sheets using pull down tabs to direct data
Goal: to enter data on sheet1 in a gridded area covering lines 2-60 and B-AO that can be sorted (currently using a table). In cell C I would like a pull down tab that has a list of sheets 2 through 34 to choose from, in cell D I would like a second pull down tab that directs what line on that sheet the data is transfer to. The data per line item that I want to capture would include all of the info from cell 2A through 2AO, 3A through 3AO, etc... To sum up I would like to enter in data about a specific product in a master sheet on sheet1 Line 2B through 2AO. Once entered I would click on cell 2C and select sheet7, followed by clicking on the 2D's pull tab and selecting line 10 which would put it into the kitchen section. Once I've clicked on those I should be able to go to sheet7 and see the same info on line 10. In the future I could select sheet8 in cell 2C and it would move to sheet8 line 10. Struggling to figure out, thank you for any help
1 Reply
Hello,
this will be difficult to achieve if the data is spread across several sheets.
Put all the data into one sheet, one contiguous table, and add a column that specifies what sheet/category each row belongs to.
Then you can easily build a pivot table that shows all the columns of the data and you can filter it with a slicer or a filter dropdown by the value in the sheet/category column.
The approach you describe is cumbersome and can possibly be achieved with the Indirect() function, but Indirect() is volatile and will make your worksheet very slow to react if you have a lot of formulas.
