I am currently working on an Excel 2010 workbook with a worksheet for each region I do business in containing the estimated man hours for the next X weeks, with a final worksheet adding the sum of each week for each region.
I have completed the majority of the workbook with just one formula holding me back:
=SUM(San_Diego!F32+Inland_Empire!F19+Los_Angeles!F26+Over_The_Horizon!F18)
The above formula returns the correct SUM perfectly but automatically corrects itself when someone adds a new column into one of the worksheets. I have attempted a few other options that have been recommended over the past week I have been working on this workbook:
=SUM(San_Diego!$F$32+Inland_Empire!$F$19+Los_Angeles!$F$26+Over_The_Horizon!$F$18)
The $ does not work at all and still auto corrects.
I've also tried adding INDIRECT but that doesn't work either and gives a #REF error
=SUM(INDIRECT("San_Diego!E32"&"Inland_Empire!E19"&"Los_Angeles!E26"&"Over_The_Horizon!E18"))
The cells being referenced do have the following simple SUM formula or an equivalent in them:
=SUM(E3:E17)
I'm hoping (and quite sure) that I have just totally missed a concept and it is a simple error I am making over and over.
Have you tried:
=SUM(INDIRECT("San_Diego!E32")+INDIRECT("Inland_Empire!E19")+INDIRECT("Los_Angeles!E26")+INDIRECT("Over_The_Horizon!E18"))
or maybe with Fs rather than Es?