How to copy variable data from 2 worksheets into a seperate 3rdworksheet

[stephen.carter2]

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

 

[Max]

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.

 

[stephen.carter2]

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










- 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

 

[Max]

welcome, glad to hear you got it working, Stephen.
Thanks for feeding back

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

 

[stephen.carter2]

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

 

[Max]

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

 

[stephen.carter2]

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

 

[Max]

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

 

[stephen.carter2]

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

 

[Max]

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]

Then you could propagate it by copying D1 across to G1, copying D1:G1 and
pasting onto D3:G3, and so on, as desired

---

 

[stephen.carter2]

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

 

[Max]

welcome, Stephen

---

Max,

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

Stephen