link data from multiple workheets

[Guest]

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

 

[David Biddulph]

=INDIRECT("'sheet "&ROW()&"'!B2")

 

[Guest]

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

 

[David Biddulph]

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.