Consolidating dynamic data within the same workbook

[JonDalton]

I'm struggling to achieve something and am sure I am not using the right tool for the job.

I am trying to create a dashboard for a report that will have slicers taking data from multiple worksheets. It has to be in the same sheet as the data which is generated from a link to our accounting system. I cannot alter this portion.

I have 2 tabs, Actual, and Budget. These have the exact same column headers, the main difference that I'll be using to segment charts is that their 'Ledger' column, is either A (Actual) or B (Budget).

The problem is I've never gathered dynamic data from multiple worksheets in the same workbook when there is no relationship between the two to link them. If this didn't look good and feel fast I'd just Index through each set of data on a new sheet or something stupid like that but this has 130k+ rows.

Actions so far:

Created 2 named ranges, these must pull in the dynamic data, the columns should never change but this was mostly a Googled formula and my modifications weren't selecting beyond A:A. Row number must be -1 in order to omit the totals row generated.
'Actual'=Actual!$A$1:INDEX(Actual!$1:$1048576,COUNTA(Actual!$A:$A)-1,COUNTA(Actual!$1:$1))
'Budget'=Budget!$A$1:INDEX(Budget!$1:$1048576,COUNTA(Budget!$A:$A)-1,COUNTA(Budget!$1:$1))

Create a PivotTable from the PivotTable Wizard from Multiple Consolidation Ranges. With or without defining 'pages' (never touched this area of Excel before) it does not generate useful data (ie. not a consolidated list of all transactions, instead it has duplicate values across every row)


I've also tried messing around with adding the data to the data model but I have no idea how to link them when no relationship can be dragged across.

What am I doing wrong? How should I consolidate two dynamic named data sets in order to produce charts and such?

I usually manage to either come up with a solution or the right words to Google before submitting a post but this one has gone above my head.

 

[sandy666]

try

• Excel Power Query (Get & Transform) Tutorial for Beginners
• Microsoft Power Query for Excel Help
• Google: Power Query

 

[JonDalton]

try

• Excel Power Query (Get & Transform) Tutorial for Beginners
• Microsoft Power Query for Excel Help
• Google: Power Query

doh.

I tried that initially but considered it a failure as when I dropped down the Ledger row it only showed 'A' after appending the queries.

'Loaded more' and found 'B'. First time working with larger sets of data..

Thank you!

Created 2 tables for Budget and Actual. Created 2 queries QBudget/Qactual, Appended queries as new and loaded it into a new sheet. This should work!

 

[sandy666]

I don't see your structure of data so I can't say more but if Append will work for you that's ok.
You can try not load Append into the sheet but use it As Connection then use PivotTable from Query Table

 

[JonDalton]

I don't see your structure of data so I can't say more but if Append will work for you that's ok.
You can try not load Append into the sheet but use it As Connection then use PivotTable from Query Table

After testing it works dynamically I did this, thank you! (I assume this will speed up the sheet)

 

[sandy666]

You are welcome