Solved

Display formula in another cell

Posted on 2013-12-03
6
227 Views
Last Modified: 2013-12-03
I am still having Excel 2010. I have 2 worksheets, PriceList and BOM.

In my BOM worksheet - i want to show the exact cell reference from PriceList

for example: show as "PriceList!F4" rather than the value. Anywhere to achieve this?

Thanks in advance...
0
Comment
Question by:Keng0499
6 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39692265
One way is to use a user-defined function with a worksheet formula like:
=CellFormula(B2)                     where cell B2 contains a formula

Put the code in a regular module sheet. You will find the function listed under category "User defined functions" in the function wizard (fx icon to left of formula bar).
Function CellFormula(cel As Range) As String
If cel.Cells(1, 1).HasFormula Then CellFormula = Mid(cel.Cells(1, 1).Formula, 2)
End Function

Open in new window

0
 
LVL 81

Expert Comment

by:byundt
ID: 39692271
Sample workbook showing the user-defined function:
CellFormulaQ28308825.xlsm
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39692273
Will  "PriceList!F4" be the result of a specifi query, like the cell that contains a search value?

regards, barry
0
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

 
LVL 32

Expert Comment

by:Rob Henson
ID: 39692284
If you want to just see the formula in the cells you can press "Ctrl + `" to convert from values to formulae, pressing again will convert back.

The ` is top left of the keyboard above Tab key.

If its more of a permanent fix that you want, try this.

Check the area of the formulas required to see if they are absolute (with $) or relative (without $). If already absolute then you can skip the next step.

For relative formulas highlight the areas required and assuming they all refer to a separate sheet use Find and Replace to replace " ! " with " !$ " (without the double quotes and spaces).

Copy the column of formulae across to another column and the references won't change.

Then find and replace " = " with " '= ", this will convert all the formulae to text.

Hope that helps.
Thanks
Rob H
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39692306
Formula -> name manage -> new --> Price --> =PriceList!F4
0
 
LVL 81

Expert Comment

by:byundt
ID: 39692312
If you are using INDEX & MATCH formulas to return values from worksheet PriceList, you can use a modified version of my user-defined function to capture the cell reference. You will still use a formula like:
=CellFormula(B2)

The trick that I am using will work with INDEX & MATCH formulas because they return a reference to a cell. It will not work with VLOOKUP formulas because they merely return a value.

The code as written will return a cell reference if the formula points directly to a cell. If not, it will return the formula.

Revised code:
Function CellFormula(cel As Range) As String
Dim frmla As String
Dim targ As Range
frmla = Mid(cel.Cells(1, 1).Formula, 2)
On Error Resume Next
Set targ = Application.Evaluate(frmla)
On Error GoTo 0

If targ Is Nothing Then
    CellFormula = frmla
Else
    CellFormula = "'" & targ.Worksheet.Name & "'!" & targ.Address(False, False)
End If
End Function

Open in new window

CellFormulaQ28308825.xlsm
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you how to use shortcut menus in the Access run-time environment.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question