Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Adding the active worksheet name to a formula

    Dear loungers,

    As an occassional "power-user" I forget how to do things, this is my current headache...

    I have a name that is a formula. The formula is this:
    =FIND(":",'$C8)+2
    This works fine as a formula if typed into a cell so should work in a name. And it does but it adds the sheet name like this:

    =FIND(":",'Sheet1'!$C8,1)+2

    I need the formula to be generic i.e. usable in any sheet in the workbook, and ultimately I want it in a Range Name.
    So I thought I could use a function to get the worksheet name:

    Function WSNAME()

    Application.Volatile
    WSNAME = ActiveSheet.Name

    End Function
    The function works perfectly. But I can't work out how to use it to get the formula to "resolve" to e.g. =FIND(":",'Sheet1'!$C8,1)+2. And ultimately how to get it in a Range Name

    Any ideas, or simpler ways to do this?

    Thank you.............................liz

  2. #2
    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
    You can use indirect to have it ignore the worksheet
    =FIND(":",INDIRECT("C8"))+2

    Steve

  3. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thank you...

    New problem is that this formula should adjust depending on the row. However the suggested:
    =FIND(":",INDIRECT("C8"))+2
    Will always be referencing C8, whereas it should be C8, C9, C10 dependent on th current row. If I wasn't using INDIRECT but a direct refenrce this would work but gives me the original problem of the worksheet name. I seem to be back at square one!

    liz
    Last edited by lizat; 2011-10-16 at 10:36.

  4. #4
    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
    Does this work. It should lock on the c column but change with the active row:
    =FIND(":",INDIRECT("C"&row()))+2

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Dear Steve,

    I also thought this would work but once in a Range Name it returns a VALUE error, so although it works in a cell as a formula it will not work once in a Name. I have tried several variations and so far failed. I wonder if I should approach the problem completely diffenrtly but am not sure how?

    liz

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Liz,

    Could I ask why you want it as a Named Range?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Liz,

    Could I ask why you want it as a Named Range?
    Hi Retired Geek!

    Because it will be used in many places across several sheets and in addition to having only one copy of the formula it also makes formulae using it more readable. The whole suite will copy, sort and delete duplicates from a source report and needs to be repeatable. I have most of it cracked and I do have a version that works without this but, for me, this would make it better, more maintainable and probably smaller.

    liz

  8. #8
    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
    I can confirm that the named formula I provided does not work (I apologize for not checking it before posting, it was one of those things I thought I understood well enough to post directly...)

    The "=INDIRECT("C"&row())" works correctly as a named formula, so the issue seems to be that named formula does not like find (or search) with the indirect and I have no idea what the reason for the failure is (but named formulas have been known to be quirky at times).

    I suspect that the named formula (as you envision it) will not work. Some options:
    1) Create local (instead of one global) named formula, one for each sheet all with the same name referring to $C<row> in the current worksheet
    2) Don't even bother with the named formula and just use a regular formula (it is short enough and should not impact performance that much. I don't think there is much impact on the size of this formula multiple times vs the named formula multiple times. Named formulas can save space if you use one as an array for multiple cells
    3) Create a User-defined function to do it. This would look the simple but be the slowest (UDFs are slower than built-in formulas) and also consume memory like the other ones.

    I recommend #2 as the simplest, most straightforward and most intuitive method.

    Steve

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Quote Originally Posted by sdckapr View Post
    3) Create a User-defined function to do it. This would look the simple but be the slowest (UDFs are slower than built-in formulas) and also consume memory like the other ones.
    I can confirm that this method does work, as I was going down this route when Steve posted his original formula.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    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 could also use:
    =FIND(":",INDIRECT("RC3",0))+2
    Regards,
    Rory

    Microsoft MVP - Excel

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

    sdckapr (2011-10-17)

  12. #11
    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
    Rory,
    Nice job. I did not think about using an RC type reference and this seems to work exactly as the OP wanted! I will need to keep this in mind.

    Steve

Tags for this Thread

Posting Permissions

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