Formula for gathering data from many sheets, that includes new sheets as they're added?

[RecordMan]

Hi guys,

So I have a workbook that I'm creating new sales orders in.
It has a front page that summarises things like total profits etc.

In the cell for totals profits I currently have this formula which references a range of sheets:

=SUM('sale#3:sale#1'!P1)

I don't want to have to update this formula every time I add a new sheet.
Is there a more general way to reference "all sheets"? Perhaps sheets with a certain prefix in their name (eg: "sale#")?
So that as I add new sheets with this prefix they are included automatically?

I hope this makes sense?
I have attached the workbook so you can see what I mean.

[6StringJazzer]

In the cell for totals profits I currently have this formula which references a range of sheets:

=SUM('sale#3:sale#1'!P1)

There is nothing like this in the file you attached, and there is only one sheet.



Using a 3D reference with a range of sheets is based on the order of the sheet tabs. Based on your description I would create a dummy sheet that is always the rightmost tab, and one in the leftmost tab, then in insert new sheets between the dummies. Then your 3D formula should work with no further changes.

Please Login or Register  to view this content.

[RecordMan]

Hello again Jeff,

My apologies - I hadn't saved that file when I uploaded it, so lots of things I mentioned were missing - as you noticed.
(I have uploaded a more complete version now)

I like your thinking with the "dummy" sheets. Nice idea!

[6StringJazzer]

Thanks for the rep!

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."