Excel SUM (across mutiple worksheets) Function Returns #REF When Using Indirect

0
votes

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.

1
excelexcel-formulaexcel-indirect
Have you considered using PowerPivot. Summing things across multiple worksheets seems more difficult and error-prone than I would think it needs to be to get the job done. I know you probably already have your system set up to make everything work, but I think this is a great opportunity to try out PowerPivot. – mmarie
I have never herd of PowerPivot before, It looks like its just an add on to Excel correct? Will all users who want to view the file I create need to download the add on before viewing? This file will be going out to multiple people at multiple locations and most of the individuals are not tech savvy in ANY way. – Mr Madigan
Yes, PowerPivot is a free add-in for Excel. According to MR. Excel users that want to manipulate the file must have PowerPivot. If you put the file on SharePoint (that has the PowerPivot add-in), then users can open the Excel file through the browser and do not have to have PowerPivot. (link: mrexcel.com/forum/powerpivot-questions/…) There is a great explanation and some helpful tips at: powerpivotpro.com/what-is-powerpivot – mmarie

1 Answers

0
votes

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?