Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    returning the formula from another cell (2000 SP3)

    Is there a worksheet formula which can return the formula contained in another cell, as a string?

    This is trivial to do with a custom function in VBA, but this is on behalf of a user who can't use VBA for their solution.

  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

    Re: returning the formula from another cell (2000 SP3)

    I do not know of way to do this unless you convert the formula to text and read the string (which defeats the purpose) or a custom function.

    The Custom function could be in the personal.xls file or an addin if the only reason for not wanting to use VB is the macro warning. Macros in Personal or addins do not trigger the warning.

    Steve

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

    Re: returning the formula from another cell (2000 SP3)

    You can.

    1. Select cell B1
    2. Define this name (this assumes you want the formula in the cell immediately to the left of the cell you are currently in):
    Name:
    GetFormulaLeft
    RefersTo
    =GET.CELL(6,A1)
    3. Now in cell B1 enter
    =GetFormulaLeft

    Enter the formula above in any cell and the cell will show the formula of the cell to the left of it.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  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

    Re: returning the formula from another cell (2000 SP3)

    It seems that never having to learn the XL macro language before VB puts me at a disadvantage for knowing some of these "tricks". <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Steve

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

    Re: returning the formula from another cell (2000 SP3)

    I never have either, but a few guys at the forum I started frequenting back in 1996 had. They learnt me these tricks.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: returning the formula from another cell (2000 SP3)

    Wow, that's superb. Thank heaven for Macro language!

    Thanks,

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

    Re: returning the formula from another cell (2000 SP3)

    There is one drawback to this solution though.

    Do NOT copy a cell containing that formula to another sheet, or Excel may crash.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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