Excel Help for fairly new user

%3CLINGO-SUB%20id%3D%22lingo-sub-1993647%22%20slang%3D%22en-US%22%3EExcel%20Help%20for%20fairly%20new%20user%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1993647%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20an%20older%20worksheet%20from%20a%20excel%20document%20some%20else%20created%20that%20has%20multiple%20pages.%26nbsp%3B%20I%20have%20saved%20this%20as%20a%20new%20document%20for%20me%20to%20make%20changes%20since%20this%20is%20a%20new%20job%20from%20the%20original%20document.%26nbsp%3B%20%26nbsp%3BThe%20formula%20works%20good%20until%20I%20get%20to%20the%206th%20page%20of%20the%20worksheet.%26nbsp%3B%20I%20am%20calculating%20data%20from%20week%20to%20week%2C%20keeping%20up%20with%20man%20hours%20worked.%26nbsp%3B%20For%20instance%2C%20week%20one%20(page%201)%20they%20work%20700%20hours%20(column%20BC)%2C%20week%202%20(page%202)%20they%20work%20800%20hours%20(column%20BC)%2C%20I%20need%20week%202%20(page%202)%20to%20show%20they%20worked%20a%20total%20of%201500%20hours%20in%20column%20DE%2C%20etc.%26nbsp%3B%20I%20need%20this%20to%20continue%20to%20each%20page%20in%20the%20worksheet%20through%20the%20job%20time%20frame.%26nbsp%3B%20Each%20week%20I%20am%20using%20dates%2C%20Dec%207%2C%202020%20(column%20B)-Dec%2013%2C%202020%20(column%20C)%2C%20etc.%26nbsp%3B%20%26nbsp%3BI%20am%20hoping%20this%20makes%20sense.%26nbsp%3B%20Please%20forgive%20me%20if%20not!%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1993647%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1993759%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%20for%20fairly%20new%20user%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1993759%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F903899%22%20target%3D%22_blank%22%3E%40sumrall90%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20it%20might%20help%20if%20you%20know%20the%20elements%20of%20the%20formula%20used%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%2BB6%2B(%40'Dec%2014-20'!D6%3AE6)%3C%2FP%3E%3CP%3Etranslates%20to%3C%2FP%3E%3CP%3E%22Add%20the%20cell%20B6%20in%20this%20worksheet%20to%20cells%20D6%20through%20E6%20on%20the%20sheet%20'Dec%2014-20'%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20change%20that%20formula%20manually%2C%20or%20you%20can%20click%20in%20the%20total%20cell%2C%20type%3A%3C%2FP%3E%3CP%3E%2B%3C%2FP%3E%3CP%3Eclick%20B6%3C%2FP%3E%3CP%3Etype%20%2B%3C%2FP%3E%3CP%3Eclick%20the%20cell%20in%20the%20other%20sheet%20to%20add%20to.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that's%20clear%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20c%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1993944%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%20for%20fairly%20new%20user%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1993944%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F903899%22%20target%3D%22_blank%22%3E%40sumrall90%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20seems%20that%20you%20added%20a%20number%20to%20the%20formula%20in%20sheet%20(Jan%2018-24)%2C%20see%20the%20attached%20photo.%3CBR%20%2F%3EI%20fixed%20the%20formula%20to%20be%20like%20the%20one%20on%20the%20first%20sheets.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%221.png%22%20style%3D%22width%3A%20812px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F241068i17368A7D02E3DEC7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%221.png%22%20alt%3D%221.png%22%20%2F%3E%3C%2FSPAN%3Ees%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I am working on an older worksheet from a excel document some else created that has multiple pages.  I have saved this as a new document for me to make changes since this is a new job from the original document.   The formula works good until I get to the 6th page of the worksheet.  I am calculating data from week to week, keeping up with man hours worked.  For instance, week one (page 1) they work 700 hours (column BC), week 2 (page 2) they work 800 hours (column BC), I need week 2 (page 2) to show they worked a total of 1500 hours in column DE, etc.  I need this to continue to each page in the worksheet through the job time frame.  Each week I am using dates, Dec 7, 2020 (column B)-Dec 13, 2020 (column C), etc.   I am hoping this makes sense.  Please forgive me if not!  

7 Replies

@sumrall90 

 

I think it might help if you know the elements of the formula used:

 

=+B6+(@'Dec 14-20'!D6:E6)

translates to

"Add the cell B6 in this worksheet to cells D6 through E6 on the sheet 'Dec 14-20'"

 

You can change that formula manually, or you can click in the total cell, type:

+

click B6

type +

click the cell in the other sheet to add to.

 

Hope that's clear

 

To c

@rhancock I did try typing it in manually and it would not work (put in correct information from formula).  I even tried to copy and paste the formula and just change the dates.  I guess what I am not understanding is why it is working on the first 5 pages and then on the 6th it does not calculate correctly.  I am sure it is something that I have done!  Were you able to look at the document? Thank you for your help!!

@sumrall90 

 

It seems that you added a number to the formula in sheet (Jan 18-24), see the attached photo.
I fixed the formula to be like the one on the first sheets.1.pnges

@Jihad Al-Jarady Thank you so much for your help!!!  May I ask you another question please.  If and when I need to add more pages to the document (to add more weeks) and I need the information to continue as is, how do I get the numbers to continue to add to the totals in the new page that I need to add?  Again, thank you so much for your help!!!!

@Jihad Al-Jarady  Also, can you tell me what I am doing wrong when I am trying to manually type in the formula?  I have tried to copy and paste the formula and then manually type it in and it will not let me, brings up the save to a folder screen or gives me an error message?

Thank you!!!!

@sumrall90 

 

I am glad that worked for you.

Regarding your how to do it with the new sheets, you can use just copy the one of the ready sheets and change the sheet name in the formula,

or if you have a lot of sheets you will need VBA code (not sure if there is simpler way) so it is better you write this question as a new post.


@Jihad Al-Jarady Thank you!!