help with lookup

R

Robert Gillard

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
 
P

PC

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
 
M

Max

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top