SOLVED

Sum across multiple sheets when adding sheets with dynamically changing sheet names?

%3CLINGO-SUB%20id%3D%22lingo-sub-2772196%22%20slang%3D%22en-US%22%3ESum%20across%20multiple%20sheets%20when%20adding%20sheets%20with%20dynamically%20changing%20sheet%20names%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2772196%22%20slang%3D%22en-US%22%3E%3CP%3ENot%20sure%20if%20I%20am%20trying%20something%20that%20is%20impossible%3F%26nbsp%3B%20I%20have%20a%20timesheet%20tracking%20workbook.%26nbsp%3B%20Each%20sheet's%20name%20changes%20dynamically%20to%20a%20job%20number%2C%20when%20I%20enter%20the%20job%20number%20into%20a%20cell%20at%20the%20top%20of%20each%20individual%20sheet.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20below...%20update%20cell%20E2%20to%20%22MJN%20Job%203%22%20the%20sheet%20is%20automatically%20named%20%22MJN%20Job%203%22%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Budman361530_0-1632267612224.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311898i9EBD8B47B686A354%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Budman361530_0-1632267612224.png%22%20alt%3D%22Budman361530_0-1632267612224.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Budman361530_1-1632267724103.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311899i6C34F54E9AC700C6%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Budman361530_1-1632267724103.png%22%20alt%3D%22Budman361530_1-1632267724103.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ECurrently%20I%20have%2010%20sheets%20in%20my%20work%20book.%26nbsp%3B%20%26nbsp%3BWe%20have%20grown%20past%20this%2C%20and%20some%20weeks%20we%20have%20well%20over%2010%20jobs.%26nbsp%3B%20I%20want%20to%20make%20a%20Macro%20to%20add%20sheets.%26nbsp%3B%20That%20part%20is%20easy.%26nbsp%3B%20However%2C%20all%20the%20sheets%20total%20on%20one%20summary%20sheet.%26nbsp%3B%20%26nbsp%3BMy%20current%20code%20in%20the%20summary%20sheet%20is%20simple...(example%20above)%20%3D'test%20test'!D42%2B'MJN%2021MJNtest'!D42%2B'MJN%20Job%203'!D42...%20etc.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20I%2C%20or%20is%20it%20possible%20to%20have%20a%20formula%20that%20would%20know%20to%20grab%2Fadd%20all%20of%20Cell%20D42's%2C%20from%20what%20ever%20sheets%20are%20between%20let's%20say%20sheet%20%22MJN%20Job%201%22%2C%20and%20sheet%20%22MJN%20Job%20100%22...%20depending%20how%20many%20sheets%20I%20may%20add%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2772196%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2772244%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20across%20multiple%20sheets%20when%20adding%20sheets%20with%20dynamically%20changing%20sheet%20names%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2772244%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F533335%22%20target%3D%22_blank%22%3E%40Budman361530%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAdd%20the%20sum%20formula%20into%20the%20desired%20cell%3C%2FSPAN%3E%3C%2FP%3E%3COL%3E%3CLI%3E%3CSTRONG%3ELeft%20click%3C%2FSTRONG%3E%26nbsp%3Bon%20the%26nbsp%3B%3CSTRONG%3EFIRST%26nbsp%3B%3C%2FSTRONG%3Esheet%20with%20the%20mouse.%3C%2FLI%3E%3CLI%3E%3CSTRONG%3EHold%20Shift%3C%2FSTRONG%3E%26nbsp%3Bkey%20and%26nbsp%3B%3CSTRONG%3Eleft%20click%3C%2FSTRONG%3E%26nbsp%3Bon%20the%26nbsp%3B%3CSTRONG%3ELAST%26nbsp%3B%3C%2FSTRONG%3Esheet.%20Now%20select%20the%20cell%20%3CSPAN%3ED42%3C%2FSPAN%3E%26nbsp%3Bin%20the%20%3CSTRONG%3ELAST%26nbsp%3B%3C%2FSTRONG%3Esheet.%20Add%20a%20closing%20bracket%20to%20the%20formula%20and%20press%26nbsp%3B%3CSTRONG%3EEnter%3C%2FSTRONG%3E.%20Your%20sum%20formula%20should%20now%20look%20like%20this.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EConsidering%20the%20names%20you%20gave%20us%2C%20your%20formula%20will%20look%20like%20that%3C%2FP%3E%3CP%3E%3DSUM('test%20test%3AMJN%20Job%203'!D42)%3C%2FP%3E%3CP%3EIn%20other%20words%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DSUM('FirstSheet%3ALastSheet'!D42)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReplace%20FirstSheet%20and%20LastSheet%20with%20the%20worksheet%20names%20you%20wish%20to%20sum%20between.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20can%20change%20this%20to%20be%20more%20dynamic%2C%20making%20it%20even%20easier%20to%20use.%20Instead%20of%20using%20the%20names%20of%20the%20first%20and%20last%20sheets%2C%20you%20can%20create%20two%20blank%20sheets%20to%20act%20as%20bookends%20for%20your%20calculation.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JulianoPetrukio_1-1632271636535.png%22%20style%3D%22width%3A%20577px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311909i766F74E901876B58%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JulianoPetrukio_1-1632271636535.png%22%20alt%3D%22JulianoPetrukio_1-1632271636535.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2773778%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20across%20multiple%20sheets%20when%20adding%20sheets%20with%20dynamically%20changing%20sheet%20names%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2773778%22%20slang%3D%22en-US%22%3EUgh!!!!%20I%20was%20so%20close.%20I%20had%20two%20extra%20%22%20'%20%22%20(apostrophes)%20in%20my%20equation.%20I%20was%20typing%20%3DSUM('first%20sheet'%3A'last%20sheet'!D42)%20and%20could%20not%20figure%20out%20why%20that%20wasn't%20working.%20Was%20wondering%20if%20it%20just%20couldn't%20be%20done.%20Works%20now...%20thank%20you!!%20Have%20an%20awesome%20day!%3C%2FLINGO-BODY%3E
Occasional Contributor

Not sure if I am trying something that is impossible?  I have a timesheet tracking workbook.  Each sheet's name changes dynamically to a job number, when I enter the job number into a cell at the top of each individual sheet.  

Example below... update cell E2 to "MJN Job 3" the sheet is automatically named "MJN Job 3"

Budman361530_0-1632267612224.png 

Budman361530_1-1632267724103.png

Currently I have 10 sheets in my work book.   We have grown past this, and some weeks we have well over 10 jobs.  I want to make a Macro to add sheets.  That part is easy.  However, all the sheets total on one summary sheet.   My current code in the summary sheet is simple...(example above) ='test test'!D42+'MJN 21MJNtest'!D42+'MJN Job 3'!D42... etc.   

Can I, or is it possible to have a formula that would know to grab/add all of Cell D42's, from what ever sheets are between let's say sheet "MJN Job 1", and sheet "MJN Job 100"... depending how many sheets I may add?

 

 

 

 

2 Replies
best response confirmed by Budman361530 (Occasional Contributor)
Solution

@Budman361530 

Add the sum formula into the desired cell

  1. Left click on the FIRST sheet with the mouse.
  2. Hold Shift key and left click on the LAST sheet. Now select the cell D42 in the LAST sheet. Add a closing bracket to the formula and press Enter. Your sum formula should now look like this.

Considering the names you gave us, your formula will look like that

=SUM('test test:MJN Job 3'!D42)

In other words

 

 

=SUM('FirstSheet:LastSheet'!D42)

 

 

Replace FirstSheet and LastSheet with the worksheet names you wish to sum between.

 

We can change this to be more dynamic, making it even easier to use. Instead of using the names of the first and last sheets, you can create two blank sheets to act as bookends for your calculation. 

 

JulianoPetrukio_1-1632271636535.png

 

 

Ugh!!!! I was so close. I had two extra " ' " (apostrophes) in my equation. I was typing =SUM('first sheet':'last sheet'!D42) and could not figure out why that wasn't working. Was wondering if it just couldn't be done. Works now... thank you!! Have an awesome day!