Solved

Display formula in another cell

Posted on 2013-12-03
6
234 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 33

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 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