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.
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.