Solved

Display formula in another cell

Posted on 2013-12-03
6
208 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 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
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 80

Expert Comment

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

Expert Comment

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

regards, barry
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
Formula -> name manage -> new --> Price --> =PriceList!F4
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Outlook Free & Paid Tools
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now