Excel: How to Combine Multiple Sheets into One with Dynamic Ranges Without Losing Formulas?
I'm trying to combine data from multiple sheets into a single summary sheet in Excel. Each individual sheet contains a different number of rows, and I want to ensure that any formulas in those sheets remain intact in the summary sheet. I've been using Power Query to append the data, but my main scenario arises when the source sheets have varying column structures. For instance, I have three sheets named `Sales_Q1`, `Sales_Q2`, and `Sales_Q3`. Each sheet contains a header row with different numbers of columns. For example, `Sales_Q1` has columns A to C, while `Sales_Q2` has A to D, and `Sales_Q3` has A to E. I want to create a summary sheet that consolidates all this data dynamically. I tried using the following Power Query code: ```m let Source1 = Excel.CurrentWorkbook(){[Name="Sales_Q1"]}[Content], Source2 = Excel.CurrentWorkbook(){[Name="Sales_Q2"]}[Content], Source3 = Excel.CurrentWorkbook(){[Name="Sales_Q3"]}[Content], Combined = Table.Combine({Source1, Source2, Source3}) in Combined ``` However, this approach leads to columns mismatches, and I end up with null values wherever the columns don't align. Additionally, if I make changes to the formulas in any of the source sheets later on, they do not reflect in the summary. I've also attempted to use the `INDIRECT` function to reference cells in the summary sheet, but that results in the `#REF!` behavior when rows are deleted from the source sheets. How can I dynamically combine these sheets without losing the formulas and ensure the summary sheet updates correctly with any changes? For context: I'm using Excel on Ubuntu. The stack includes Excel and several other technologies. Has anyone dealt with something similar?