Desktop

Ask something
Discussion Locked

Formulas visible in Excell Spreadsheet

0 helpful
3 replies

by sarahsway

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a worksheet at work that I would like to display formulas in but only in a specific column. I seem to only be able to display the formulas used in the sheet on the entire worksheet. Is there any way to display these formulas for a specific column while the rest of the workbook displays the results in text?

Thanks, Sarah

3 replies

by dklippert

Here's an answer from John Walkenbach

http://tinyurl.com/d0ng

"You need to create a very simple custom worksheet function using VBA. The
steps below assume Excel 97 or later.

1. Press Alt+F11 to activate the VB Editor
2. Select your project in the Project window
3. Choose Insert Module to insert a new VBA module
4. Enter this function into the VBA module:

Function CellFormula(c) as String
CellFormula = c.Formula
End Function

Re-activate Excel by pressing Alt+F11 again. You can then use this new
function in a formula. Here's an example that displays the formula in cell
A1:

=CellFormula(A1)

If A1 does not have a formula, the function returns the cell's value. If
you'd like the function to display an empty string if the cell does not have
a formula, use this code:

Function CellFormula(c) As String
If c.HasFormula Then
CellFormula = c.Formula
Else
CellFormula = ""
End If
End Function

by sarahsway

Thank you for a quick response. Yes I was able to utilitze the function provided. Very helpful. Thanks

This discussion is closed to new replies.