Dynamic Weekly Graph to run from Monday to Sunday?

[Tarvalon]

Is there a way to create a dynamic chart to automatically run from the most recent Monday to Sunday. So on Monday the entire chart will reset?

Thanks In Advance.

 

[doublej_jj]

The formula below will give you the most recently passed Monday.

=NOW()-(WEEKDAY(Now())-IF(WEEKDAY(Now())=1,-5,2))

As for the rest of your question, there are many different ways to go. I suggest putting all of your data on a different tab and then using VLookup or SumProduct to pull data corresponding by date.

Jeff

 

[Tarvalon]

I appreciate your help. That is way advanced for me. I copied/ pasted that formula into the spreadsheet and it worked just fine. I don't know where to start to make it work for me in a dynamic graph AND chart.

I assume I would use it somewhere to tell the graph and charts to start there and then use a 7 day dynamic chart. I just don't know what to do with that formula.



Thanks,

Tarv

 

[doublej_jj]

If you put that formula in Cell A2 (always giving you the latest Monday), and then
Cell A3 = A2 + 1 (This is Tues)
Cell A4 = A3 + 1 (This is Wed)
Cell A5 = A4 + 1 (Etc)
Cell A6 = A5 + 1 (Etc)
Cell A7 = A6 + 1 (Etc)
Cell A8 = A7 + 1 (Etc)

You now have your dates along the left hand side of your chart. The next trick is data in your chart. Maybe Column B is # Widgets Made and Column C is # Windgets Sold.

On a separate tab called Data, I'd have
Column A = Date
Column B = Widgets Made
Column C = Widgets Sold

Back in your chart,
Cell B2 = Vlookup(A2,Data!$A$2:$C$1000,2,0)
Cell C2 = Vlookup(A2,Data!$A$2:$C$1000,3,0)

{These formulas find the dates in your chart over in your data and pulls back the corresponding widgets sold or made}

Copy B2 and C2 down through row 8. This creates a dynamic chart of data where the Monday is almost the most recent passed, the rest of days follow in line, and the data is based on what you recorded on your data tab.

Attach a graph based on that chart and when the data changes, the graph will change.

There are some deep ideas here if this isn't part of your "normal" Excel skills. Try to duplicate the simple idea I gave and learn from it.

Jeff

 

[Tarvalon]

OK. I see what you're saying now. I put in the formulas for the days and they work fine. Now I need help with my vlookup. It is returning N/A.

=VLOOKUP(Schedule!A3:A94,Schedule!A2:AH93,6,0)

I need it to match the date in column A on "Hours" table (the one with the Monday formula) look that date up in column A on the "Schedule" table and return the matching value in column F.

Thanks again.

Tarv

 

[Tarvalon]

I'm sorry that formula is returning the value for the day before.

This formula:

=VLOOKUP(A3,Schedule!A1:K92,6,0)

is returning n/a.

Thanks.

 

[doublej_jj]

Your formula:

VLOOKUP(Schedule!A3:A94,Schedule!A2:AH93,6,0)

That should be a single cell, the one that you want the data to come from. A single day, etc.

That should be where the data is kept. It sounds like this is right.

More like

Vlookup($A3,Schedule!$A$2:$AH$93,6,0)

Good Luck.

Jeff

 

[Tarvalon]

Returning N/A

 

[doublej_jj]

The value in A3 is not an exact match for anything in Schedule A2:AH93

Try to move this formula to a Hidden / Non-Used area

NOW()-(WEEKDAY(Now())-IF(WEEKDAY(Now())=1,-5,2))

Let's say Cell Z1 for now

Where it used to be put

=Date(Year(Z1),Month(Z1),Day(Z1))

THe first formula has a time associated with it and your Schedule tab probably just has dates. This will get your dates in your charts to just have dates.

Jef

 

[Tarvalon]

GOT IT!!! THANKS A MILLION!!