Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Kent, Kent, United Kingdom
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formulas. How can you change a worksheet ref ? (Excel 97)

    I'm looking for some clever ways to be able to chnage the worksheet reference within a formula.
    This comes from having some formulas that ideally need to have the reference within them changed... i.e =Sum('worksheet1!'AA10:AA30) to =Sum('brandnew!'AA10:AA30)

    Does anybody have any advice about how to write these formulas so that it's easy to change the worksheet reference... Perhaps even write the formula so it refers to a reference cell that the user can ammend?

    All comments welcome. Any level of complexity.

    Cheers

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas. How can you change a worksheet ref ? (Excel 97)

    Try using the INDIRECT function:

    =INDIRECT("'" & A1 & "'!B2")

    This gets you cell B2 of the sheet who's name is in cell A1.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Kent, Kent, United Kingdom
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas. How can you change a worksheet ref ? (Excel 97)

    Thanks.

    I had tried this but found it wasn't all that satisfactory as the formulas get very long and complicated.
    I've also looked into Names - but with more than three named data ranges this gets tedious - FAST

    I've got a fix with a VBA workaround on an after update on a reference cell.

    Any other ideas that people might have? (I'm curious - I assume this is a farly common problem for Excel users out there)
    - I suppose this is where good design is worth it's wait in gold [img]/forums/images/smilies/tongue.gif[/img]

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formulas. How can you change a worksheet ref ? (Excel 97)

    I think Jan Karel's =INDIRECT() solution is best. Have you thought about breaking the =INDIRECT() arguments into separate cells, such as separate cells for the WB name, separate cells for the Sheet names and separate cells for the Range Names?

    Alternatively you could try Find-&-Replace Macros that work on the necessary formulas; they won't be simple to structure particularly if the formulas are complex.

    Pehaps you can give us an idea of what you are trying to accomplish?
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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