Referencing Cells in Multiple Worksheets

[Graham]

I have a workbook with multiple worksheets. I need to get the value of cells
in all of the sheets. I know I can simply enter =Sheetname!A1 etc but this
is hugely time comsuming and there are a lot to get through. I can manage to
list all the sheet names on one worksheet, but opposite each of these names
I need the value of A1 for example in each sheet.ie
=Sheet1!A1
=Sheet2!A1
=Sheet3!A1
=Sheet4!A1 etc in a list on the main sheet.
Is there an easy way to do this. Grateful for any help.

Kind Regards,
Graham Haughs
Turriff, Scotland

 

[Frank Kabel]

Hi
if your sheetnames are in column a enter the following in column B
(cell B1)
=INDIRECT("'" & A1 & "'!A1")
and copy down

 

[Bob Phillips]

You can avoid listing the sheet names if they are all of form Sheet1,2,3,
etc, and use

=INDIRECT("Sheet"&ROW(A1)&"!A1")

and copy down.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Graham]

Thanks to you both. You have no idea the time that you have just saved me
with that information.
Very grateful for your quick responses.

Graham