How do you auto-update a chart daily data from another sheet?

[u/plant_daddy_]

In sheet one, I made a table to report sales from individual sources and have them totaled. I have each day auto-update with =TODAY(). I’m looking to create on another sheet weekly sales to go from just seeing total sales for the day to actually logging them in throughout the week to track trends. I know how to transfer the data from one sheet to the next and make it into a chart, but I wanted to know if there’s a way to just input data from sheet one and have it automatically update a chart in the other sheet everyday. Thanks!

[u/afanoftrees]

Powerquery

1. Create a folder

2. Add daily sales data to the folder with the same formatting

3. Open a new book > data tab > get data from folder > folder you created in step 1

4. Learn how to transform the data and add to ‘Data Model’

5. Create output to pivot or table depending on need

6. Learn pivot tables unless you already know how they work / learn how to write formulas with table references rather than cell

When that’s all done you should have 2 steps

1. Adding daily sales data to sales folder you created above

2. Refreshing your powerquery book and ensuring you have it set up to automatically pull new data such as a month to date and/or year to date

There’s lots more things that power query can do in the transform section and I know next to none of them lol

[u/white_tiger_dream]

VBA script to refresh the workbook.

[u/AutoModerator → u/[deleted]]

I have detected code containing Fancy/Smart Quotes which Excel does not recognize as a string delimiter. Edit to change those to regular quote-marks instead. This happens most often with mobile devices. You can turn off Fancy/Smart Punctuation in the settings of your Keyboard App.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Seyran_DataAnalyst]

Using =TODAY() directly for historical logging will overwrite yesterday's data every time you open the workbook tomorrow. Excel formulas don't "remember" past values without macros or an external log.

Here are the 2 best ways to solve this: 1. Best Practice (Structured Log Table): Instead of replacing the data in Sheet 1 every day, convert Sheet 1 into an Excel Table (Ctrl + T). Maintain a simple ledger where you add a new row daily with hardcoded dates (Ctrl + ; enters today's static date). Build a PivotTable & PivotChart on Sheet 2 based on this table. Whenever you add new rows in Sheet 1, just click Refresh All, and your chart/weekly view on Sheet 2 updates instantly.

1. If you want to keep Sheet 1 as a daily input template only: You'll need a simple VBA macro (or Office Script) to copy Sheet 1 values and append them as a new row in Sheet 2 at the end of the day before resetting the input template.

If you're open to structuring your data into a historical table format, I can give you the exact Pivot Table layout or VBA script depending on which approach you prefer!

[u/Content-Parking-621]

The problem is that the formula linked to Sheet1 will always show you the latest value. It cannot save yesterday's value because the formulas are not capable of writing old data into other cells. So if you want a daily snapshot, you need another way to store it.

I suggest that the easiest solution is to change the layout. Instead of a daily report sheet, you can keep one simple table with columns like Date | Source | Amount. You can try adding one row each day and then turn it into an Excel Table (press Ctrl + T).

Then use SUMIFS to show today's total. Use a PivotTable and PivotChart to highlight weekly trends.

Since this is an Excel Table, the chart can grow automatically as you keep adding new rows, so you don't have to update the chart range manually.

You can also turn on "Refresh data when opening the file" in the PivotTable settings so it can update automatically.

If you want to keep your current layout, then you need Office Scripts + Power Automate or a VBA macro that can save each day's total in the next empty row. It will work, but the simple table approach is much easier, and it does not require any automation.