Automated formula documentation (Excel 2000)

[zeddy]

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

[sdckapr]

Try this function in a module:

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


Steve

[pieterse]

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.

[unkamunka]

<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?

[pieterse]

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.