automatically base a chart on next columns each day?

  • Thread starter Thread starter neowok
  • Start date Start date
N

neowok

basically i have a big spreadsheet with dates accross the top for th
next couple of months, and a bar chart taking its values from th
column for yesterdays date.

At the moment Im having to go in each day and change the source data t
the next column manually. What I really need is a way of calculatin
which row the chart should look at based on todays date. i.e. today i
the 19th so ive got it based on AC3 to AC22. tomorrow i need it t
base itself on AD3 to AD22 etc etc. the date is contained in row 2.

In addition to this i need to make a weekly one so i need something t
make the weekly one calculate based monday to sunday of the previou
week according to todays date.

because these days will probably go right up to the end of the year
making some sort of loop like if A2=today then base chart on A2, if A3
today then base chart on A3 etc etc is going to end up taking ages t
go through the loop so this approach is out.

any help much appreciated
thanks
 
Dynamic charts based on dynamic ranges:

http://peltiertech.com/Excel/Charts/Dynamics.html

I would put a row with the appropriate dates at the top of the columns,
so today's column is headed with today's date. I'd set up a dynamic
range that used offset and a match function to identify the proper column.

Let this be the data, in A1:D11

7/19/04 7/20/04 7/21/04

a b c
a 4 11 7.5
b 5 10 7.5
c 6 9 7.5
d 7 8 7.5
e 8 7 7.5
f 9 6 7.5
g 10 5 7.5
h 11 4 7.5

I named B1:D12 "when" and A4:A11 "myX". I defined a couple dynamic ranges:

Name: myY
Refers to:
=OFFSET(myX,0,MATCH(INT(NOW()),when,0))

Name: myName
Refers to:
=OFFSET(myX,-1,MATCH(INT(NOW()),when,0),1,1)

I edited the series formula to look like this:

=SERIES(Sheet1!myname,Sheet1!myX,Sheet1!myY,1)

Alternatively, in the Source Data > Series tab, select the series, and
enter these for Name, X Values (categories), and Y Values:

Sheet1!myname
Sheet1!myX
Sheet1!myY

Excel converts to Sheet1! to Book1.xls!, but we don't usually need to
worry about this.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
thats more or less what we have. weve got dates along the top datin
back to june 25th and going up to august (currently).

i need to automate the production of a graph based on todays date, an
another based on the last weeks dates from last monday up until an
including sunday.

its based on 20 rows of 'planned' and underneath that 20 rows o
'actual' the 20 rows refer to 20 stations so its basically planne
figures for stations 1-20 then undeneath that actual figures fo
stations 1-20 and im making a dailing grpah showing the planned an
actual for each station for that one day with x being the 20 statio
names and a planned and actual column for each, and a weekly showin
the overall totals for that week with x being monday to friday.

im just trying to automate it to keep the daily graph pointing at th
right date column and the weekly pointing at the right week because th
people printing them off dont know enough to be messing with th
sheets.

in your example from what i understand, 'myx' is the 20 station name
(these will be static and not move though so i can just base the
names on the actual cells). looks like myY does a search for th
entire table for todays date but im a bit unclear as to what exactly i
returns. the table will also grow so the best thing would be if
could base the 'when' on the whole table, whoever big or small i
gets.

Ill see what i can figure out.

thank
 
MATCH looks for today's date in the top column, and returns the index of
the cell containing the date within the dates in the range named 'when'.
So if 'when' is the dates 7/19/04, 7/20/04, 7/21/04, the match looks for
today (7/20/04) and returns the index 2. You can define 'when' in any
way that makes sense for your data.

myY moves to the right of myX by the MATCH result above. If you've lined
things up properly, myY refers to the column just below today's date in
the header column.

This is just one example of using dynamic ranges for charting. It isn't
complicated, but you have to generate one or two before it really sticks.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
right I think i get it now.

i was trying to create my own two tables on a hidden sheet and copy th
values for the week into this table somehow from the main data table
then just set my charts to be based on this new table, the weekly usin
this table. then have another table based on this weekly one which i
just one days totals put into this little table by looking at today
date and comparing it to the weekly table (which would have days a
headers) saying if today = "mon" then use mondays column etc. to ge
the right days data into the small 1 day table, then base the dail
chart on this.

i tin your approach is probably simpler. as i couldnt work out how t
get the weekly totals for the correct week into my weekly table.

although with your approach im still not sure how i can make the weekl
one work. perhaps the simplest way is somehow make it base itself o
the last 7 days from whatever todays date is, then just print it off o
a monday and itll contain last weeks data from last monday to sunday.

Thanks ill try and fiddle with it tomorrow
 
perhaps the simplest way is somehow make it base itself on the
last 7 days from whatever todays date is, then just print it off on
a monday and itll contain last weeks data from last monday to sunday.
Click to expand...

That's what I was thinking. One of my examples shows how to do the last
12 months; doing the last 7 days means just changing the counter, eh?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
hahaaaaaaaa

luvly jubly. managed to sort the daily and the weekly. fiddled aroun
with your formulas a little to make it work how I wanted but work
perfectly now. Had to create a hidden table for the weekly chart agai
using your modified formulas, as it needs to be in a slightly differen
format than the daily one, I then based the chart on that table.

Even managed to dynamically name each chart with the correct dates s
its completely automated now.

Thanks for ya help, saved me a lot of hassle now
 
Back
Top