Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Help with indirect formula

    Hi loungers

    I'm having problems getting an indirect formula working

    In the attached sample I get a #REF! error in cell D4. I got it working in I4.

    I guess it may have something to de with dates

    Any thoughts and assistance would be much appreciated.

    Regards
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verada,

    Excel doesn't like the - in the Sheet names. If you remove the dashes, e.g. Jan14 then
    =INDIRECT(CONCATENATE(TEXT(B4,"mmmyy"),"!b3"))
    works just fine!
    verada.JPG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks RG that looks to do what I'm after

    Regards

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You don't actually need to change the name - as with any other 'special' names, you just need to enclose the sheet name in single quotes:
    =INDIRECT("'"&TEXT($B$4,"mmm-yy")&"'!B3")
    Note: your original formula didn't work simply because it was passing the actual date value (01/01/2014), not the correctly formatted one (Jan-14).
    Regards,
    Rory

    Microsoft MVP - Excel

  5. The Following User Says Thank You to rory For This Useful Post:

    RetiredGeek (2014-12-04)

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    Thanks that's a much better answer!

    Verada,

    Here's my formula (if you prefer concatenate to &) corrected:
    =INDIRECT(CONCATENATE("'",TEXT(B4,"mmm-yy"),"'!b3"))

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Out of interest, why would you ever use CONCATENATE in preference to '&'?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Maybe someone that doesn't post here might use Concatenate if they're being paid by the hour. More to type.

    I also find it interesting the MS called it Concatenate rather than Catenate. Both English words mean "to connect in a series of ties or links; to form into a chain."

    Catenate, I think, is more often used in a biological context, but still means "to chain."

    I found that in the programming language APL, chaining is the "," (like when forming a list in English). And, it's called "catenate."
    In APL, it would be A,B,C to chain the contents of the three variables together. If A and B, for example, were two matrices with an equal number of rows, A,B would catenate them side-by-side.

    What's interesting (to me, anyway) is that if A is a 4 by 5 and B is a 4 by 10, and A,B was assigned to the variable C, the resulting array would be a 4 by 15. Then, if you were to write: ,C it would (un)ravel the 2-dimensional array into a vector (1D) and APL calls that "ravel" versus "unravel."

    Just another viewpoint.

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    In a word "clarity". I've always tended toward being verbose in my coding. Over the years I've used many different languages with of course different conventions/syntax. So if there is a way to be clear about what is going on I tend to choose that method (you know the old Hungarian Notation thing) because 6 months and many different projects later I want to know what I was doing at a glance vs trying to figure out what does that code do anyway.

    Kevin,

    As to being paid by the hour the same thing applies it may take me longer to write it initially but come maintenance/update time, and it always comes, it pays its dividends.

    Of course as always YMMV!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    RG: I hope you realized I was kidding about the paid by the hour comment.

  11. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    Yeah I got it but I thought I reply for those who might not!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #11
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Guys for you help and explanations - I'll try both options.

    Regards

  13. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    RG,
    This isn't code though.

    Personally I find & a lot easier to read in a formula.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    I agree it is a PERSONAL thing.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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