Auto Populate a Table based on values entered in a single cell

[J_D_B]

Hi all

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.

Regards

J

[#2 · Accepted solution]

Hi @J_D_B 

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)

Note:
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

[#3]

Hi @L z.

This is exactly what I need, thank you.

I am struggling to see how you created this doc as I am not seeing any formulas on the SummaryHisto sheet that explains how you managed to get the table to update everytime the date changes (or is this a trade secret lol?)

But this is exactly what I was looking for. Thank you, I really appreciate all your work and help with this.

Kind Regards
J

[#4]

@J_D_B 

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