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

On Jan 30, 4:24*pm, "Max" wrote:
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
Singaporehttp://savefile.com/projects/236895
xdemechanik
---
Max, That's great, but what about the second issue about thew downward
cells as below.

When I drag the cell the values in the rows and column part of the
formula increments, but I require it to stay at A$:A or 1:1

Many thanks

Stephen

I'm not sure on your issue there.

Post a link to your sample to show
clearly how you want it to propagate.

You could use:
http://www.freefilehosting.net/
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote
Max, That's great, but what about the second issue about thew downward
cells as below.

When I drag the cell the values in the rows and column part of the
formula increments, but I require it to stay at A$:A or 1:1

Many thanks

Stephen

Max,
Again sorry for the delay.
http://www.freefilehosting.net/download/3be6a
is the link.
So, on sheet 'C' I've cut and pasted the cell D1 into Cell D3.

The reference to the cells B1/C1 is still in the formula so this has
to be edited at present to show ref's B3/C3.
But I'd like this to be automatically picked up.

But at the same time in the formula, the reference to the ROWS part
has incremented to show (1:3) where i'd like it to remain at (1:1).

This cells would then be copied across as needed (range d3 : g3) and
once done d3 copied to d5 and so on.

Any help as normal will be gratefully recieved,
Thanks
Stephen

Ah, think you just need to amend the formula in D1 slightly to:
=OFFSET(INDIRECT("'"&$B1&"'!A"&$C1),,COLUMNS($A:A)-1)
That should do it.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
...
Max,
Again sorry for the delay.
http://www.freefilehosting.net/download/3be6a
is the link.
So, on sheet 'C' I've cut and pasted the cell D1 into Cell D3.

The reference to the cells B1/C1 is still in the formula so this has
to be edited at present to show ref's B3/C3.
But I'd like this to be automatically picked up.

But at the same time in the formula, the reference to the ROWS part
has incremented to show (1:3) where i'd like it to remain at (1:1).

This cells would then be copied across as needed (range d3 : g3) and
once done d3 copied to d5 and so on.

Any help as normal will be gratefully recieved,
Thanks
Stephen

Then you could propagate it by copying D1 across to G1, copying D1:G1 and
pasting onto D3:G3, and so on, as desired
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

On Feb 5, 3:03*pm, "Max" wrote:
Then you could propagate it by copying D1 across to G1, copying D1:G1 and
pasting onto D3:G3, and so on, as desired
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
--- *

Max,

Again, sorry for the delay in repling, but a million thanks for help
you have given me.

Stephen

welcome, Stephen
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote

Max,

Again, sorry for the delay in repling, but a million thanks for help
you have given me.

Stephen