Oct 17 2023 03:53 AM
Oct 17 2023 03:53 AM
Hope someone will be able to help / not sure if its possible
As far as possible without using VB (as this is my Achilles heel though I can understand if there is no other way). I have a template that draws in data and summarizes it from data that I copy in paste in a separate sheet with headings A, B, C and D.
Below is a very simplified version of what I have
so all the values (B2:B8, C2:C8 etc) have formulas that calculate values from raw data on another sheet.
In the end I then summarize everything for heading A - D in row 9.
The raw data is updated weekly / bi-weekly and then this table is updated by calculating the values automatically with the excel formulas in each cell.
What I want to do is create another sheet, that every time I update the raw data, and this table is then updated, it will take the values from B9, C9, D9 and E9 and add them to a table on another sheet without me having to copy and paste them over every time.
So it will start creating a table on another sheet where every time this is updated it will automatically add it below the last entry in the other sheet as I dont want to risk the data being updated and me missing it, and not copying the values over to the table as there is no fixed date when they update the raw data.
If anything is unclear I will try to elaborate but if anyone has a link to a YouTube tutorial / have figured this out I will really appreciate it.
Oct 19 2023 12:43 AMSolution
In any case you'll need - somewhere in your workbook - something like a Date that indicates when your Raw Data was last updated
Attached is a Get & Transform aka Power Query sample. To play with it:
- Update data in 'Raw Data'
- Change Last update date
2 ways to see the Summary addition in 'SummaryHisto':
Go to Data (tab) > Refresh All
OR Close the workbook (saving your change) > Re-open it (the query that updates SummaryHisto is configured to auto-refresh when the file opens)
Additions to 'SummaryHisto' only takes place if the Last update date doesn't already exist in SummaryHisto. In other words, you can do as many Data > Refresh All you want nothing will be added to 'SummaryHisto' if Last update date already exists in the History
Oct 19 2023 02:12 AM
Oct 19 2023 02:47 AM
I am struggling to see how you created this doc as I am not seeing any formulas on the SummaryHisto
There's no Excel formula, it's all done with Power Query. To see (and Edit) the queries:
- Go to Data (tab) > Queries & Connections (the corresponding pane opens on the right)
- Double-click on a query name to Edit it
(Power Query functions doc. is available here)
Something I forgot to mention is my previous post: if no date is enterred in RawData!Last update, SummaryHisto doesn't update