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

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

On Jan 28, 6:31*pm, "Max" wrote:
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
Singaporehttp://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- Hide quoted text -

- Show quoted text -

Max,

In initially, I thought the formula was wrong but after a bit of
playing I offer you many thanks.
I have to add and amend a few lines here and there but it will get me
through what I need to do.

Stephen

welcome, glad to hear you got it working, Stephen.
Thanks for feeding back
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
<stephen.carter2.. wrote
Max,

In initially, I thought the formula was wrong but after a bit of
playing I offer you many thanks.
I have to add and amend a few lines here and there but it will get me
through what I need to do.

Stephen

Snipped for space.

In initially, I thought the formula was wrong but after a bit of
playing I offer you many thanks.
I have to add and amend a few lines here and there but it will get me
through what I need to do.


Max,
A few questions.
1. I gave you the wrong info, worksheet info is in cells A1 and B1. A1
being the sheet and B1 the row number.
2. In C1 I need to have the info in from the Cell A1 on the original
sheet.
This works ok, but when I drag the cell C1 to C2 the ref in the
orignal formula still keeps the first A$1$ and B$1$ the same and I
have to re reference the cell to A$2$ and B$2$

First question is how can I get the cell values to increment so that
different cells are picked up from different sheets.

Second question is when I drag the cell the values in the rows and
column part of the formula increment but I require it to stay at A$:A
or 1:1

The answer at present is to do it manually but any help in get a
working formula would be great recieved.
Thanks
Stephen

For clarity, let's assume you have the sheetnames in A1 down,
the actual cell refs in B1 down, eg

in A1: X, in B1: A1
in A2: Y, in B2: E5

You could place this in C1:
=INDIRECT("'"&A1&"'!"&B1)
and copy down to retrieve
in C1, the equivalent of: =X!A1
in C2, the equivalent of: =Y!E5
and so on
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote
Max,
A few questions.
1. I gave you the wrong info, worksheet info is in cells A1 and B1. A1
being the sheet and B1 the row number.
2. In C1 I need to have the info in from the Cell A1 on the original
sheet.
This works ok, but when I drag the cell C1 to C2 the ref in the
orignal formula still keeps the first A$1$ and B$1$ the same and I
have to re reference the cell to A$2$ and B$2$

First question is how can I get the cell values to increment so that
different cells are picked up from different sheets.

Second question is when I drag the cell the values in the rows and
column part of the formula increment but I require it to stay at A$:A
or 1:1

The answer at present is to do it manually but any help in get a
working formula would be great recieved.
Thanks
Stephen