Odd INDIRECT behavior on Mac

Question

Background: I have a workbook with multiple sheets in same format in order to collect similar data for different work efforts. Workbook also has one sheet that aggregates Total data from all sheets. Because the number of sheets can change month to month, and because the name of a given sheet can change, I use INDIRECT to pull data from each sheet. Workbook is updated by two people: one using Windows, the other using a Mac.

Problem: After the Mac user updates the workbook one of the INDIRECT statements errors out, but not the others. Based on observing history, I'm confident that it only happens after the Mac user updates but only sporadically (doesn't happen every time the Mac user updates).

Specifics:

In the sheet that aggregates data from the other sheets in the work book:

Column J contains =IFERROR(@INDIRECT("'"&$C9&"'!"&"B19"),0)

Column K contains =IFERROR(@INDIRECT("'"&$C9&"'!"&"C19"),0)

Column L contains =IFERROR(@INDIRECT("'"&$C9&"'!"&"D19"),0)

When the error occurs, it occurs in column K and always in column K, never in J or L. The error never occurs with the Windows user.

I haven't removed the IFERROR to capture the specific error code because it's more disruptive when the column contains non-numeric data.

I do not know whether the Mac user is updating in the browser or in the app.

Additional info: I've stated the problem as though it were one workbook and one Mac user, but the reality is that there are multiple workbooks (same format) and multiple Mac users. It's only the workbooks with the Mac users that experience this (periodic) issue.

Answer 1

The formulas shown above are the full formulas (see screenshot below). The whole point is to aggregate the values from multiple sheets onto one sheet - no other manipulation required. I'm using INDIRECT so that users can add new worksheets (in addition to moving or renaming) without having to recreate the formulas to pull the data from the new sheet.

Answer 2

It seams there must be more to your formulas; as they stand right now they're not doing anything except referencing cell on sheets. Can you show us the full formulas?

Almost all functions can be run against 3-D ranges without INDIRECT. For some functions you can capture a 3-D range even if the sheets are moved around or renamed, without INDIRECT.