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

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

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

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
----