Results 1 to 5 of 5
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts

    Automated formula documentation (Excel 2000)

    Hi All

    I have a column which contains various formulas.
    In the adjacent column to the right, I would like to document the cell formula on the left.
    The 'document' cell format I like is: space followed by semi-colon followed by space followed by the cell's formula content on my left.
    I can cut and paste the cell formula into a text string which is OK.
    But if new rows are added or inserted, the formula's column content will adjust whereas the text documentation alongside doesn't.
    So, is there a simple way say, of using a 'documenting' formula to do what I want?

    Any help much appreciated!

    zeddy

  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: Automated formula documentation (Excel 2000)

    Try this function in a module:

    <pre>Function DocumentCell(rng As Range) as String
    DocumentCell = " : " & rng.Cells(1).Formula
    End Function</pre>


    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: Automated formula documentation (Excel 2000)

    Just to show an alternative method without using a macro:

    Define this name (WHILST on CELL B1):

    Name: LeftCellFormula
    Refers to: =" : " & GET.CELL(6,Sheet1!A1)

    Now in a cell enter : =LeftCellFormula

    It will show the formula of the cell immediately to the left.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automated formula documentation (Excel 2000)

    <hr>" : " & GET.CELL(6,Sheet1!A1)<hr>
    I don't seem to be able to stumble across this in the Online Help. (Presumably the use of the colon is a visual reminder that the cell contents are a String.) Can you provide an indication as to how this is derived?
    Gre

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

    Re: Automated formula documentation (Excel 2000)

    The ":" is just there because it was in the little piece of code provided in an earlier answer in this thread. It can be omitted.

    The GET.CELL function is in fact an ancient XL4 macro function. Some clever soul discovered one can use them in defined names. They can do all sorts of interesting things. Download my arg2name.zip from the Excel MVP page below for a more complicated example <g>.

    The accompanying help file is still available for download here.
    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
  •