98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » How do I extract data from a dynamic range? | Excel forum
How do I extract data from a dynamic range? | Excel forum
Resolved · Medium Priority · Version 365
Thu 21 Nov 2024, 15:59Delegate Ismail said...
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?
Fri 22 Nov 2024, 09:06Trainer Jens said...
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
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Shortcuts for working with named ranges in ExcelIf you are working with or creating named ranges in your spreadsheets, then you may find the following shortcut keys useful. |
