Auto Update 7 Day Historical Chart
So close on this one.
I have a file (attached), with a Date Range in Column A, and a Defects figure in Column B. I want the resulting chart to be a 7 day historical bar chart of the defects.
Currently I use
'OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)'
and
'OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)'
as named ranges in the chart source data to auto update the chart every time a new value is added in columns A and B. How can I modify these formulas (or create new ones) to ONLY include the new value, and the previous 6?
Thanks in advance!
`Engineers08
Define the Date range like this:
=OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$A:$A)-8,0,7)
and the Defects range like this
=OFFSET(Date,0,1)
There's no need to do the whole offset calculation again for the second range. In fact, if you ever need to change your offset parameters, you only need to apply the changes to the Date range and the Defects range will pick up the same start row and height etc.
hth
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking theicon below the post.
Avoid pie charts with more than two data points. Why? See here (pdf, 559 kb). The only acceptable pie chart is here.
Thank you!
SOLVED!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks