Show Formula in a cell

– Selva V Pasupathy, HSBC Global Resourcing, Hyderabad1
Today someone asked me how to display a formula in a cell. What a silly question? It’s Easy, just enter an apostrophe (‘) in front of the formula and one can see the formula. Oh No! But if you don’t want to disturb the cell where the formula resides? Oh! So you want to show the result of the formula as well… Yes! at the same time a user should be able to see the formula as it is in a different cell. Isn’t it simple? Enter into the cell, copy the formula starting “=” and till the end and paste it in a different cell and add apostrophe in front of it. But what if, you have thousands of formula to show? Now, there is something to work on…. Let me share what i tried sometime back and i think this should work!

Open a new Excel Workbook, press alt+F11, if you dont see a small window with the names of the sheets in a workbook, click ctrl+R… Now you will be able to see a window with all the workbooks and its contents like sheets, userforms, & codemodules. Right click the new workbook project and choose insert –> module… Now double click the module that has just been inserted… Do you see code window? If you are not, press F7 key and you will see the code module. Now in this module, copy the following code and paste it.


Option Explicit
Public Function ShowFormula(ByRef iCell As Range)
If iCell.HasFormula = True Then
ShowFormula = iCell.Formula
Else
ShowFormula = ""
End If
End Function

Now, you are ready to show the user the formula as it is wherever you want. just type "=showformula(" and then the reference to a cell to display the formula of that particular cell.

eg., =showformula(e6)

To download an example workbook, CLICK HERE

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: