Forum Discussion
Insert table data from one of 4 sheets into master invoice based on dropdown choice
I am using Excel 2016. I have a master invoice I have created, and we have 4 sheets of data, 1 sheet per quarter where assets are to be tracked. On the master sheet I have created a drop down with all of the names of each sheet (Q1, Q2, Q3, & Q4). I want the user to be able to select one of the quarters from the dropdown, and that Qtr's table contents be inserted into the invoice under the mailing information. I have successfully created each worksheet and the drop down is working. I just can't figure out how to display the data for the entire column in the master invoice, just the first row (14). I just can't figure out the best way to do the entire column. NOTE: the column will not have the same number of rows all the time so it needs to be fluid. There may be a cleaner way to do this, I just can't find info.
All of the quarter tables, Q1 - Q4 have the same formatting/layout as the data shown under the blue headings on the screenshot. On Row 14 I am able to select a quarter from the dropdown and get that row. This is what I used to do that (for column 1 row 14), I just can't find how to get the entire column to display for each heading:
=INDIRECT("'"&$B$2&"'!A2")
I was also wondering if there was a way, or if it would be better to use the Insert Date - from sheet using the drop down and if that would be better but not sure if that would be the best approach. I am new to this type of workbook.
Thanks in advance! Let me know if it needs further explanation!
4 Replies
- Try "Filter" formula if it is available with "Sort". I have used it to populate a list for each main header having multiple and different count of sub branches. It works fine. I just started using it recently.
- Right now the headers on the master invoice are just text. So I assume I would change those text headers to a formula that somehow references the sheet that the user has chosen from the drop down? Trying to picture what Filter/Sort would do in this scenario. I want the entire table to insert for a particular quarter that is chosen.
- I took the whole data and referred it to a cell which has list/dropdown and it reflects all the column in front of the selection from original data
