SUM(Indirect(.... displays #Value I have a workbook with 30+ spreadsheets. In on

Question

SUM(Indirect(.... displays #Value

I have a workbook with 30+ spreadsheets. In one of my spreadsheets I reference two (2) other spreadsheets, namely the "Setup" spreadsheet and "HasMyData"

The "Setup" spreadsheet contains the cells that are 'pointers' to the spreadsheet "HasMyData" that has the rows I want to total.

Here's my Excel equation in my Sheet1 spreadsheet where I'm attempting to SUM values from one column to another

=IF(SUM(INDIRECT("HasMyData!"&E9&Setup!$B$6:F9&Setup!$B$6))=0,0,SUM(INDIRECT("HasMyData!"&E9&Setup!$B$6:F9&Setup!$B$6)))

For example, in Sheet1 E9 I have FL (that's the starting column in HasMyData) and F9 has FT (F9 is the ending column in HasMyData).

Setup!$B$6 has 5 for Row 5 in the HasMyData spreadsheet. So, I want to sum(HasMyData!FL5:FT5)

In going through the Evaluate Formula, the "HasMyData!" properly resolves the E9 to FL. The $Setup$B$6 does not BUT in a cell with a single function (=Setup!$B$6) is has the correct value (in this case the number 5).

So, I want my IF(SUM(INDIRECT... to resolve to

IF(SUM(HasMyDataFL5:FT5)=0,0,SUM(HasMyDataFL5:FT5))

What am I doing wrong here?

Thanks for help !!

Explanation / Answer

Hi..

Try this formula and see if it works...

=IF(SUM(INDIRECT(("HasMyData!"$&E9&SETUP!B6&":"&F9&SETUP!B6)))=0,0,SUM(INDIRECT(("HasMyData!"&E9&SETUP!B6&":"&F9&SETUP!B6))))

Hope this Helps !

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.