In Z,

In A1: X
In A2: 1900

Then in A3:
=OFFSET(INDIRECT("'"&$A$1&"'!A"&$A$2),ROWS($1:1)-1,COLUMNS($A:A)-1)
Copy A3 down & across as needed.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
...
Hi all,
I'll try again as I clicked on the wrong buttom last time and posted
half a question.

I have 3 worksheets in one book, X, Y and Z.

In X and Y are data, and in A1 of each is variable data, I have done a
workbook search and identified several lines with the data I want. eg
1900 - 1930 on sheet X, 2000 - 2100 on sheet Y

I now need to copy the data to sheet Z.
So I thought :-
In A1 to ZZ1, put the sheet letter either X or Y
In A2 to ZZ1, put the line number eg 1900
So that in A3 I have a formula that pulls the value from A1! (for the
sheet) a(column in that sheet) ref the cell number A2 then I would
copy A3 to A4 and on to A20 (the range of the information needed.)

But as you can guess I've hit a problem, I can not even get passed the
first part of referencing the worksheet as when I add A1! it look for
a file called A1 - any ideas as this is driving me mad.

Many Thanks