Column Chart Update automatically for all worksheets?

[Guest]

I have a workbook that contains 9 worksheets. The first worksheet contains
all of the sales data broken down by each of our warehouses. The other 8
worksheets shows a column chart for each of our 8 warehouses by month. When
I enter the next month sales information into worksheet 1, I have to go
through and update each worksheet following that one so it shows the next
month in the chart. I would like to be able to update the first worksheet
and all of the others follow automatically. Is this possible and do you have
any suggestions that would help make this more automatic rather than so
manual and time consuming?

As and example, this is somewhat what the first worksheet looks like:

2/5/2007 3/5/2007 4/2/2007 5/4/2007
Status RSC
9 1 $10 $10 $7 $2
8 1 $5 $9 $6 $2
8 1 $3 $8 $5 $3
7 1 $2 $7 $4 $3
6 1 $1 $6 $4 $2
$1 $1 $1 $1

There will be 8 different sections just like this repeating down the
spreadsheet and then the other tabs will be a column chart with each RSC on a
different worksheet.

Thanks!

 

[Guest]

Thsi isn't a chart problem, but a general excel function problem. You want
to always be able to get the last column of data. The best way of doing this
is with the Hlookup function. I would put the last date in cell A1 on the
first sheet.

then on sheet of the worksheet reference this date when looking up the data
on the first sheet. Example

on sheet 2
=hlookup('sheet1'!A1,A2:CZ20,5,False)

I made the range of the table very wide (to column CZ) so as you add more
dates you don't have to change the range. I also started the table in row 2
so you can put the Last date in cell A1. For each data entry into the chart
you will change the row from 5 to the proper row for the lookup.