Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Display formula in another cell

Posted on 2013-12-03
6
Medium Priority
?
263 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

670 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