SUM across multiple sheets with variable sheet name question

[kstrick9]

Using a formula similar to this: =SUM(Sheet1:Sheet4!A1) how can I get Sheet4 to be variable where it equals the tab name of the sheet using this formula or any similar formula to acheive the same result: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) I've tried it all and can't figure this one out.

What I am attempting to do is when a new sheet is added it will have the correct sheet ranges already in place to the many subsequent rows to add together.

Thanks in advance for any help!

 

[T. Valko]

Using a formula similar to this: =SUM(Sheet1:Sheet4!A1) how can I get Sheet4 to be variable where it equals the tab name of the sheet using this formula or any similar formula to acheive the same result: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) I've tried it all and can't figure this one out.

What I am attempting to do is when a new sheet is added it will have the correct sheet ranges already in place to the many subsequent rows to add together.

Thanks in advance for any help!

Create a "sheet sandwich".

Insert a new sheet to the immediate left of the first sheet you want to include in the calculation. Name this sheet Start.

Insert a new sheet to the immediate right of the last sheet you want to include in the calculation. Name this sheet End.

\Start/\Sheet1/\Sheet2/\Sheet3/\Sheet4/\End/

The formula would be:

=SUM(Start:End!A1)

When you add new sheets that need to be included in the calculation just put them bwtween the Start sheet and the End sheet.

 

[kstrick9]

Thanks...works perfectly!

 

[T. Valko]

Thanks...works perfectly!

You're welcome. Thanks for the feedback!

 

[kstrick9]

T.Valko or others...one other question: The new issue that I see is that any sheet prior to the next sheet should not add the next sheet(s) value(s). This is for invoicing that tracks totals paid to date. Any way to back out subsequent sheets values? thanks

 

[T. Valko]

T.Valko or others...one other question: The new issue that I see is that any sheet prior to the next sheet should not add the next sheet(s) value(s). This is for invoicing that tracks totals paid to date. Any way to back out subsequent sheets values? thanks

Not sure I understand.

Can you elaborate?

 

[kstrick9]

For example, sheet1 (or Invoice 1) should not include the values of sheet2 (Invoice 2), but sheet2 needs to include the values of sheet1 since the invoiced amounts are progressive.

 

[T. Valko]

For example, sheet1 (or Invoice 1) should not include the values of sheet2 (Invoice 2), but sheet2 needs to include the values of sheet1 since the invoiced amounts are progressive.

Stll not clear.

Are you saying that each sheet has some form of this formula:

=SUM(Start:End!A1)

And that each sheet is to exclude some other sheet(s)?

 

[kstrick9]

@ T. Valko... What I ended up doing was excluding the previous sheet from the most current. The way below works but I was hoping was to find a more automated way to copy the last invoice to create a subsequent invoice where the formulas were copied over in a way that allowed the new invoices to be progressive while the previous invoices remained unchanged. If there was only a way to allow the tab name to be variable this would be possible as per my initial post. Keep in mind that these invoices are for progress payments and therefore many times invoiced amounts are as a percentage of a particular scheduled value.

Invoice 1 = 1st so no reference to other sheets
Invoice 2 =Sum(Invoice1!A1)
Invoice 3 =Sum(Invoice1:Invoice2!A1)
Invoice 4 =Sum(Invoice1:Invoice3!A1)....

Invoice 12 =Sum(Invoice1:Invoice11!A1)
Invoice Final =Sum(Invoice1:Invoice12!A1)

 

[aam1932]

thank for the 1st question on the sum up all the sheet. i want to add how to sumif between the sheet (start to end)
your current formula is sum(start:end!A1) how about sumif

thank you