updating tables automatically

[Guest]

I have a table full of historical data which is updated week by week. I want
to have another table that only shows the last 5 weeks worth of data and
updates automatically each time the historical table is updated. Is this
possible (or partially possible) in excel? If so, how do I go about doing it?

Many thanks

 

[Bernie Deitrick]

Chris,

It's easy - if you set up your tables in a certain way.

Let's say that your sheet is named "Historic Data" and that your table is in columns A to E, and
there are no blank rows in your table. Also, you put the newest data at the bottom of the existing
table, and that you need two rows for each week, for a total of ten rows.

On another sheet, in cell A2 (row 1 is for your headers) enter the formula
=INDEX('Historic Sheet'!$A:$E,COUNT('Historic Sheet'!$A:$A)-10+ROW(A1),COLUMN(A1))
and copy down for 10 rows and across for 5 columns.

Change the value 10 to reflect the actual number of rows that you want to pull from the bottom of
your data table.

HTH,
Bernie
MS Excel MVP

 

[Bernard Liengme]

Chris:
Not properly tested but here is a start:
In A1 there is a header (the word "date") in cells below (variable number) I
have some dates
In B1 there is a header (the word "item") in cells below (variable number) I
have some text
This formula displays the date 5 up from the bottom of column A:
=INDIRECT("A"&(COUNTA(A:A)-5))
Change the 5 to 4 and you get the one below that.
The corresponding text is displayed with =INDIRECT("B"&(COUNTA(A:A)-5))
Hope this helps
If you want the table on a different sheet use
=INDIRECT("Sheet1!A"&(COUNTA(A:A)-5)) assuming the original data in on
Sheet1

best wishes

 

[Guest]

Thanks Bernie,

This is exactely what I was after.

Chris