Results 1 to 11 of 11
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Stumped by INDIRECT and an external 3D reference

    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?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.

    Update: Seems that things work fine with standard references but returns REF with 3D references. This Article seems to confirm that.
    Last edited by RetiredGeek; 2011-10-11 at 20:16.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    WebGenii (2011-10-14)

  4. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Interesting newsletter!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #4
    New Lounger
    Join Date
    Oct 2011
    Posts
    1
    Thanks
    0
    Thanked 1 Time in 1 Post
    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

  6. The Following User Says Thank You to nortannoel For This Useful Post:

    WebGenii (2011-10-14)

  7. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    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")))
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Nortannoel was just quoting the newsletter you found "interesting"...

    Steve

  9. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Its' on my To Be Read pile
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  10. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    What else could we do with the N function?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  11. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  12. #10
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Nifty and useful if you have a megaformula you want to remember how it was put together.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  13. #11
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good one Steve!
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •