Getting the formula from a cell

[Khai N]

I need the reference in the cell (i.e. need to extract the formula) instead
of the value itself so I can use it in another formula. How can this be done?

 

[Gary''s Student]

Use this small User Defined Function:

Public Function whatsInIt(r As Range) As String
whatsInIt = r.Formula
End Function


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

 

[Kimmie40]

I need the reference in the cell (i.e. need to extract the formula) instead
of the value itself so I can use it in another formula. How can this be done?

copy the original formula from the fx bar, not from the cell. Paste
the formula into the new cell.

 

[Khai N]

Use this small User Defined Function:

Public Function whatsInIt(r As Range) As String
whatsInIt = r.Formula
End Function

Thanx! This was very helpfull! Works great! But to use this as an argument
in another function I need it without the equal sign. I can't use the
textfunction RIGHT() since it will convert to a textstring. Any idea?

Regards,
Khai N.

 

[Gary''s Student]

Show me exactly how you want to use it in a worksheet cell.

 

[Khai N]

Show me exactly how you want to use it in a worksheet cell.

I'm making an 'import sheet' from Excel to a consolidation program (Cognos
Controller). It takes values from a Excel sheet by using a simple reference

='Balanse 3018'!G5

However, to find the correct account to post the value in G5, Excel have to
look up the name of the account in an unsorted list. This is done by using
this command

=INDEX(Kontoer!$A$51:$B$111;MATCH('Balanse
3018'!$B5;Kontoer!$A$51:$A$111;0);2)

So far so good. But since I'm doing this for several workbooks (many
companies) and many lines in each company (P/L statement, Balance, Equity,
Assets) the prosess of updating the value reference (G5) and the argument in
MATCH(...) - $B5 is tedious (i.e. changing the row) and vulnerable to error.
I want that $B5 (that is, the row reference "5") updates automaticly when I
change the value reference so that I only need to change it one place.

Sounds easy at first, but not that easy after all!

So how can I extract the row from your UDF (whatsinit) and paste it into the
MATCH formula?