Dynamic Excel Consolidation Table
Budget: $10 – $30 AUD
I keep a live sales log in a OneDrive-hosted workbook. In my main reporting file I want a table that refreshes automatically, pulls in only the rows whose transaction dates fall between two cells (StartDate and EndDate in the report), collapses duplicate product lines into a single row, and adds their quantities together.
Please build this entirely in Microsoft Excel. Power Query seems the natural choice, but a clean VBA routine is fine if it meets the same “one-click / on-open” refresh behaviour and continues to work when the file is synced through OneDrive.
Core expectations
• The path to the external workbook must stay dynamic so it continues to resolve once OneDrive assigns it a cloud URL.
• Changing either of the date-range cells must be enough to make the summary recalculate after a manual or automatic refresh.
• The final output table should display one row per unique product (identified by the existing SKU column) with a summed Quantity column and any other original fields I specify when we start.
Deliverables
1. Finished Excel workbook containing the dynamic summary table.
2. Brief setup notes showing me where to edit the OneDrive path or table/column names if they ever change.
3. A short walkthrough (document or screen recording) so I can reproduce the solution in other reports.
I’m ready to supply sample data immediately and will be available for quick testing rounds to confirm the refresh works flawlessly from the cloud.
Please build this entirely in Microsoft Excel. Power Query seems the natural choice, but a clean VBA routine is fine if it meets the same “one-click / on-open” refresh behaviour and continues to work when the file is synced through OneDrive.
Core expectations
• The path to the external workbook must stay dynamic so it continues to resolve once OneDrive assigns it a cloud URL.
• Changing either of the date-range cells must be enough to make the summary recalculate after a manual or automatic refresh.
• The final output table should display one row per unique product (identified by the existing SKU column) with a summed Quantity column and any other original fields I specify when we start.
Deliverables
1. Finished Excel workbook containing the dynamic summary table.
2. Brief setup notes showing me where to edit the OneDrive path or table/column names if they ever change.
3. A short walkthrough (document or screen recording) so I can reproduce the solution in other reports.
I’m ready to supply sample data immediately and will be available for quick testing rounds to confirm the refresh works flawlessly from the cloud.