Home |
Search |
Today's Posts |
#1
|
|||
|
|||
help with lookup
At the moment each month a new row of data is added to the bottom of a
"RawData" sheet, this sheet holds monthly data from 2003. A Summary sheet details just the last 13 months of data. At the moment, each month I have to delete the top month and copy and paste the "new" months data on the bottom from the Raw Data sheet. The Summary sheet should show the latest 13 months records, so I would like the row A13:J13 on the Summary sheet to lookup the last row of the RawData sheet and to reflect that data. Then I want row A12:J12 to lookup the 2nd from last row on the Raw Data sheet etc through to A1:J1 which would lookup the 13th from bottom row on the RawData sheet and reflect it on the Summary sheet.. I believe I can set a formula that will always select the last 13 rows of data from the "RawData" sheet, I regret I do not know how to go about this, can anybody help please. With thanks Bob |
#2
|
|||
|
|||
Use the OFFSET function
Assuming your table of data is on Sheet2 and starts with headings in A1 and no has blank rows This should return the last row =OFFFSET(Sheet2!A1,COUNTA(Sheet2!A:A)-0,0) change the "-0" to "-1" , "-2" ... to get the preceeding rows. HTH PC "Robert Gillard" wrote in message o.uk... At the moment each month a new row of data is added to the bottom of a "RawData" sheet, this sheet holds monthly data from 2003. A Summary sheet details just the last 13 months of data. At the moment, each month I have to delete the top month and copy and paste the "new" months data on the bottom from the Raw Data sheet. The Summary sheet should show the latest 13 months records, so I would like the row A13:J13 on the Summary sheet to lookup the last row of the RawData sheet and to reflect that data. Then I want row A12:J12 to lookup the 2nd from last row on the Raw Data sheet etc through to A1:J1 which would lookup the 13th from bottom row on the RawData sheet and reflect it on the Summary sheet.. I believe I can set a formula that will always select the last 13 rows of data from the "RawData" sheet, I regret I do not know how to go about this, can anybody help please. With thanks Bob |
#3
|
|||
|
|||
Another play to try ..
Assuming sheet: RawData data is in cols A to J, from row1 down [Data in col A is assumed continuous, w/o any in-between blank cells] In sheet: Summary Put in say, A2: =OFFSET(INDIRECT("RawData!$A"&COUNTA(RawData!$A:$A )-11),ROWS($A$1:A1)-1,COLU MNS($A$1:A1)-1) Copy A2 across to J2, fill down to J13 A2:J13 will always return the last 12 rows of data from sheet: RawData as new data is added there -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Robert Gillard" wrote in message o.uk... At the moment each month a new row of data is added to the bottom of a "RawData" sheet, this sheet holds monthly data from 2003. A Summary sheet details just the last 13 months of data. At the moment, each month I have to delete the top month and copy and paste the "new" months data on the bottom from the Raw Data sheet. The Summary sheet should show the latest 13 months records, so I would like the row A13:J13 on the Summary sheet to lookup the last row of the RawData sheet and to reflect that data. Then I want row A12:J12 to lookup the 2nd from last row on the Raw Data sheet etc through to A1:J1 which would lookup the 13th from bottom row on the RawData sheet and reflect it on the Summary sheet.. I believe I can set a formula that will always select the last 13 rows of data from the "RawData" sheet, I regret I do not know how to go about this, can anybody help please. With thanks Bob |
#4
|
|||
|
|||
Oops, miss-read the latest "13" months (what was given earlier was for 12)
The Summary sheet should show the latest 13 months records Try this slight tweak to the earlier formula .. In sheet: Summary Put in A1: =OFFSET(INDIRECT("RawData!$A"&COUNTA(RawData!$A:$A )-12),ROWS($A$1:A1)-1,COLU MNS($A$1:A1)-1) Copy A1 across to J1, fill down to J13 A1:J13 will always return the last 13 rows of data from sheet: RawData as new data is added there -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Lookup Lookup | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
Excel Lookup Functions | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |