take data from several worksheets

[carolbeehive]

help, I have a spreadsheet with 52 pages and want to create a table taking (in my case) cell H10 from each sheet and putting it on a seperate sheet to create a chart, can this be done without cutting and pasting 52 times!!

[shg]

If you list the sheet names down a column (starting in, say, A1), then in B1 and copy down,

=INDIRECT("'" & A1 & "'!H2")

If the sheet names have a numeric tail (like Sheet1, Sheet2, ...), then you can enter the first name and autofill down.

[carolbeehive]

thanks for the advice but this does not work, it just returns REF
Just to be clear I have my accounts on each page for each week and my daily takings for monday in cell H10 on each sheet, and i want to create a chart that shows all my monday takings on a seperate sheet.

hope this makes it clearer

[vane0326]

thanks for the advice but this does not work, it just returns REF
Just to be clear I have my accounts on each page for each week and my daily takings for monday in cell H10 on each sheet, and i want to create a chart that shows all my monday takings on a seperate sheet.

hope this makes it clearer

May I see a small sample workbook with expected results.

[carolbeehive]

I enclose a sample of my file, if you look at the days sheet, you can see what I'm trying to do.

[EdMac]

Hi, carolbeehive

In B2
=INDIRECT("Wk"&ROW()-1&"!"&"H10")

Copy this down the weeks

For other days change H10 to the appropriate cell

HTH - lert me know how you go

Ed

[carolbeehive]

Hi Ed, thanks so much, it works, what a time saver for me. Thanks to people like you, people like me can keep learning.

[EdMac]

Thanks for the feedback - I am still learning too, glad to have helped.

Ed