link data from multiple workheets
 

I have data stored in multiple worksheets with identical layout (eg reference
no is always in cell B2 for every sheet).
I would like to summarise selected data onto a single sheet with the data
presented in rows.
I would like to insert data from sheet 2!B2 into sheet 1!B2 and then from
sheet 3!B2 to sheet 1!B3 etc.
I can create link from sheet 2 to sheet 1 but if I try to copy the link
formua down, it links sheet 1!B3 to sheet 2!B3 and not sheet 3.
Can I make the link reference jump to the next sheet each time?
is this possible?

Martin McLaughlin

link data from multiple workheets
 

=INDIRECT("'sheet "&ROW()&"'!B2")
--
David Biddulph

"Martin McLaughlin" wrote in
message ...
I have data stored in multiple worksheets with identical layout (eg
reference
no is always in cell B2 for every sheet).
I would like to summarise selected data onto a single sheet with the data
presented in rows.
I would like to insert data from sheet 2!B2 into sheet 1!B2 and then from
sheet 3!B2 to sheet 1!B3 etc.
I can create link from sheet 2 to sheet 1 but if I try to copy the link
formua down, it links sheet 1!B3 to sheet 2!B3 and not sheet 3.
Can I make the link reference jump to the next sheet each time?
is this possible?

Martin McLaughlin

link data from multiple workheets
 

Thanks David
It didn't work initally and I can't quite figure out how it works and the
significance of " and ' (it seems wuite sensetive to this.
I had never even heard of the INDIRECT commend until yesterdya but I got it
working eventually.
thanks for your help
--
Martin McLaughlin


"David Biddulph" wrote:

=INDIRECT("'sheet "&ROW()&"'!B2")
--
David Biddulph

"Martin McLaughlin" wrote in
message ...
I have data stored in multiple worksheets with identical layout (eg
reference
no is always in cell B2 for every sheet).
I would like to summarise selected data onto a single sheet with the data
presented in rows.
I would like to insert data from sheet 2!B2 into sheet 1!B2 and then from
sheet 3!B2 to sheet 1!B3 etc.
I can create link from sheet 2 to sheet 1 but if I try to copy the link
formua down, it links sheet 1!B3 to sheet 2!B3 and not sheet 3.
Can I make the link reference jump to the next sheet each time?
is this possible?

Martin McLaughlin

link data from multiple workheets
 

The double quotes " are the delimiters of a text string.
The single quotes ' are used to surround the name of a worksheet if that
name includes spaces.
--
David Biddulph

"Martin McLaughlin" wrote in
message ...
Thanks David
It didn't work initally and I can't quite figure out how it works and the
significance of " and ' (it seems wuite sensetive to this.
I had never even heard of the INDIRECT commend until yesterdya but I got
it
working eventually.
thanks for your help
--
Martin McLaughlin


"David Biddulph" wrote:

=INDIRECT("'sheet "&ROW()&"'!B2")
--
David Biddulph

"Martin McLaughlin" wrote in
message ...
I have data stored in multiple worksheets with identical layout (eg
reference
no is always in cell B2 for every sheet).
I would like to summarise selected data onto a single sheet with the
data
presented in rows.
I would like to insert data from sheet 2!B2 into sheet 1!B2 and then
from
sheet 3!B2 to sheet 1!B3 etc.
I can create link from sheet 2 to sheet 1 but if I try to copy the link
formua down, it links sheet 1!B3 to sheet 2!B3 and not sheet 3.
Can I make the link reference jump to the next sheet each time?
is this possible?

Martin McLaughlin