Ismail has attended:
Excel Intermediate course
How do I extract data from a dynamic range?
The situation is as follows,
WorkbookA:
Sheet1
Sheet2
Sheet3
Consolidated Sheet
Each sheet has someone doing data entry into a table (tables 1,2,3 for sheet 1,2,3) and I'm currently using a sort(Vstack) formula to add all the data into the Consolidated sheet and have it sorted by transaction date.
My issue is as follows.
I need to create a financial tracking aspect for the data entry, including the ability to track
Sum
Count
Sum by Account(header)
Count by Account(header)
And have the data broken down into monthly/yearly sections for ease of digestion/understanding
However i cant convert the Consolidated sheet into a table as this breaks the formula or is not able to go through
I also need to find a way for this to be added to a graph so that it will update itself. Is this possible?
RE: How do I extract data from a dynamic range?
Hi Ismail,
Thank you for the forum question.
Yes unfortunately array formulas cannot be in a dynamic table the SORT and VSTACK functions.
If you want to fully automate the task you will need to use POWERQUERY. From POWERQUERY you can connect to the 3 source tables and append and sort the data. The output you get will be in a dynamic table and then you can track and create chart using a PivotTable.
This will make the task fully automated. When someone add more data in the 3 source tables it will show in you chart and report.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
