Apr 21 2020 02:52 PM
I have a workbook with multiple sheets, and each sheet contains a single table. Each table has the same set of column headers. I am trying to consolidate and link all the tables/sheets into a single "subsheet" in the same workbook--so that all the data appears in one place for easier viewing--and I'd like new rows that are added to the source sheets to automatically update in the subsheet. I've copied each table and pasted it as a link into the subsheet. The problem is that any new rows of data added to the source sheets don't appear in the subsheet. I tried using Power Pivot but that did not achieve the result I'm trying for. Can anyone explain how to do this?
Apr 21 2020 03:36 PM
SolutionYou should use Power Query.
Select the first table and under the data tab, in the "get and transform data" area, select "from table/range". This will load PQ where you can format if you'd like. Select close and load drop down, then close and load to-then choose only create connection. Do the same for the other table. Then you need to go back to PQ, to do so, select get data, then launch PQ editor. Select table1, and choose Append Queries drop down, then select append queries as new. In the box that pops up, table1 will be selected for you, you have to choose table2 in the bottom half of the screen, then hit ok. Your mergered table will appear, and now you'll select the close and load drop down arrow, and choose close and load to Table.
When you want new data to show up in the new table, refresh all and it'll show up.
Apr 21 2020 07:22 PM
Jan 04 2023 01:42 AM
Apr 21 2020 03:36 PM
SolutionYou should use Power Query.
Select the first table and under the data tab, in the "get and transform data" area, select "from table/range". This will load PQ where you can format if you'd like. Select close and load drop down, then close and load to-then choose only create connection. Do the same for the other table. Then you need to go back to PQ, to do so, select get data, then launch PQ editor. Select table1, and choose Append Queries drop down, then select append queries as new. In the box that pops up, table1 will be selected for you, you have to choose table2 in the bottom half of the screen, then hit ok. Your mergered table will appear, and now you'll select the close and load drop down arrow, and choose close and load to Table.
When you want new data to show up in the new table, refresh all and it'll show up.