Pulling data automatically from multiple + new sheets

[scottwalker101]

I am looking for a means to pull data through from a number of different worksheets (in the same cell in each worksheet) into a summary sheet at the front of the workbook. This may involve up to 100 worksheets all pulling data in.

Is there a clever way to pull the data from that cell into the summary sheet for all the sheets one below the other which will be easy to update if I add a new worksheet to the workbook?

[mrice]

Have a look at

Consolidate data in multiple worksheets


in Excel Help.

[scottwalker101]

Martin. Thanks for that. Im now able to show the total for all the sheets in one cell.

Is there any way I can do it so the value of the cell in each sheet is show in a list format on the summary page. So for example in the 2nd sheet D3 is shown on A3 in the summary, on the 3rd sheet D3 is shown in A4 on the summary and so on?

[mrice]

try pasting

=INDIRECT("Sheet" & CELL("row",A3)-1 & "!D3")

into A3 on your first sheet and copying down. I'm assuming that your sheets are called Sheet2, Sheet3 etc.

[amcall]

I have been working on a very similar issue! This helped me out a ton. I did not know about INDIRECT().

Thanks!

-AC