Stumped by INDIRECT and an external 3D reference

Topic

New Reply

This one has me seriously stumped.
I’d like to total a series of cells holding overtime hours
normally, I’d use =SUM([WeeklyTimeSheetV4.xlsx]Week1:Week6!$K$26)
However, I’m totally them in a separate workbook and the names of the timesheets will vary with the name of the person submitting the timesheet.
With great confidence I went ahead and used:
=SUM(INDIRECT(“‘”&B2&”Week1:Week6!$K$26”)) which resulted in a ref error. And since then I’ve been trying all sorts of combinations and permutations of indirect, transpose, address, index and many others. None of which have gotten me anywhere.
I couldn’t even use indirect with a range formula in the timesheet, that totalled the values in K26 across the 6 sheets.
Ultimately, I’ve referred to a cell I’m going to hide that will total the amount in the timesheet. This works, but the fact that SUM & INDIRECT did not work the way I anticipated – bugs me.
Does anyone else have a comment or suggestion?

Reply | Quote

Replies

Catharine,

Per the help file

Note The INDIRECT function only returns the result of a reference to an open file. If a workbook that the INDIRECT function is indirectly referencing is closed, the function returns a #REF! error.

. You might also want to consult the help file for the way they construct the reference is slightly different than yours. :cheers:

Update: Seems that things work fine with standard references but returns REF with 3D references. This Article seems to confirm that.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Interesting newsletter!

Reply | Quote

hello

When the INDIRECT function operates on this array, the expected array of
values appear (by highlighting in the formula bar and pressing F9), but
for some reason this array cannot be used by Excel functions. The use of
the N function creates an array that can be used, so that the SUM function
returns the desired result.

Access platform

Reply | Quote

Hey, thanks! I hadn’t tried the N function.
Here is a sample of the formula:
=SUM(N(INDIRECT(“‘”&B2&”Week”&{1,2,3,4,5,6}&”‘!$K$26”)))

Reply | Quote

Nortannoel was just quoting the newsletter you found “interesting”…

Steve

Reply | Quote

Its’ on my To Be Read pile

Reply | Quote

What else could we do with the N function?

Reply | Quote

One “trick” one can do with it, is to annotate formulas. Since it converts text to a zero you can use it like:

=MyFormula + N(“This is an example formula”)

to annotate rather than adding a comment…

Steve

Reply | Quote

Good one Steve!

Reply | Quote

Nifty and useful if you have a megaformula you want to remember how it was put together.

Reply | Quote