Capturing Dynamic Data at Weekly Intervals

[owangillen]

Id like to find a way to capture data at weekly intervals.

The data im trying to capture are pulled from Sheet1 using basic reference formulas, and id like to see the weekly trend of how these figures are changing, on sheet2.

Background: As I manually enter daily transactions into Sheet 1, sheet2 pulls up the final values and displays them as a summary.

Ideally id like the dynamic value cells in sheet2 to be recorded weekly so i can graph these values.

Please help!

[MrChulo]

Hi There,

We would be able to help you better if you share an example of your data.

But to the sound of your question it would be better to introduce a helper column in your transactions table in sheet1. Assuming that you enter a date in a column that you call "Date", enter this formula in a new column:

Please Login or Register  to view this content.

Then you can modify your summary table to include weeknumbers and easily get the summary per week with SUMIFS. No macro needed.

Let me know if this helps.

[owangillen]

Hi MrChulo,

So ive now uploaded a sample file of what im trynna do. (sorry forgot about that)

I do the manual entries in sheet 1 (dates are on the far left). The summary sheet is sheet 2. Ideally id like a column in sheet 2 that "snapshots" and records the weekly values
of the "total Value" cell (bottom right cell). Therefore even though the "total value cell changes each time i make an entry in sheet1, ive still got a running column there which i can use to graph a trend.

Thankyou!

[MrChulo]

Hi,

So I am not sure how to solve this the best way. It is a little tricky to have a value that is constantly updating and then register the totals each week... Maybe there is someone else that can solve this better.

But here is one option with pros and cons...

Create a workbook open Event, this will check the current weeknumber and copy the value in case it is a new week, compared to the latest value.

Pros: Values are registered autmatically
Cons: you need to make sure to update Sheet1 completely during the current week. If you this week, enter values from the last week... it will not work as intended. Then you would have to enter all the values for the last week. Close the file and open it again...

Please Login or Register  to view this content.

For this I created a Table in Sheet2 that I named "WeeklyResuts", that has two columns "WeekNumber" and "Totals".

You could then make a graph on this table....

If this doesn't work and considering that it is just 1 value 1 time per month, it might be easier with a manually copy paste to this table...

What do you think?

[owangillen]

Hi MrChulo,

This worked exactly the way I wanted it to! Took me a while to figure out, since i have close to zero background in Excel, but I have managed to get it to work the way i wanted it to and im glad to say the values are saving on their own now!

I've changed the day to Sunday so the values capture on the Sunday instead of Monday. It has taken me a week to reply coz I wanted to see if it will save today, (Sunday) , which it has! Also I realized that "creating a table" meant "inserting an object" which took me 2 days to figure out, just to get the macros to run correctly.

Thanks mate!