Forum Discussion
Help with Linking information between main worksheet and another tab
Depending on your version of Excel there are a few ways to consolidate multiple sheets.
1. VSTACK - available in 365. An elegant way to rollup data quickly.
2. PowerQuery
https://support.microsoft.com/en-us/office/learn-to-combine-multiple-data-sources-power-query-70cfe661-5a2a-4d9d-a4fe-586cc7878c7d
Very efficient. Potential drawback: you'd have a live query in your workbook and would need to get your team up to speed on keeping the query up to date and basics of using a query.
3. VBA - a macro could consolidate the sheets quickly. There's a question of how you'd run the macro (Clicking a button or on a specific event like sheet activation, for example). You'd have to get your team up to speed on working with an XLSM, enabling content, etc.Of the three above, I prefer VSTACK because it would involve 1 formula put in 1 cell and pulling back many records. No security or query issues. It's only a matter of availability.
Depending on your version of Excel there are a few ways to consolidate multiple sheets.
1. VSTACK - available in 365. An elegant way to rollup data quickly.
2. PowerQuery
https://support.microsoft.com/en-us/office/learn-to-combine-multiple-data-sources-power-query-70cfe661-5a2a-4d9d-a4fe-586cc7878c7d
Very efficient. Potential drawback: you'd have a live query in your workbook and would need to get your team up to speed on keeping the query up to date and basics of using a query.
3. VBA - a macro could consolidate the sheets quickly. There's a question of how you'd run the macro (Clicking a button or on a specific event like sheet activation, for example). You'd have to get your team up to speed on working with an XLSM, enabling content, etc.
Of the three above, I prefer VSTACK because it would involve 1 formula put in 1 cell and pulling back many records. No security or query issues. It's only a matter of availability.
- Do you have access to the VSTACK function?
yes, I do. I have 365. I have never used Vstack, my quick search of it - it looks a bit daunting. Patrick2788
- I can step you through it. If you have a sample workbook that's comparable to the real workbook, I can draw up the formula.