Home |
Search |
Today's Posts |
#1
|
|||
|
|||
updating tables automatically
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 |
#2
|
|||
|
|||
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 "Chris" wrote in message ... 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 |
#3
|
|||
|
|||
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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Chris" wrote in message ... 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 |
#4
|
|||
|
|||
Thanks Bernie,
This is exactely what I was after. Chris "Bernie Deitrick" wrote: 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 "Chris" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically updating Graphs | Excel Worksheet Functions | |||
automatically updating cells in other files. | Excel Discussion (Misc queries) | |||
Automatically updating table | Excel Discussion (Misc queries) | |||
updating pivot tables | Excel Discussion (Misc queries) | |||
Automatically Sort Tables | Excel Discussion (Misc queries) |