Solved

Display formula in another cell

Posted on 2013-12-03
6
243 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 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
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.

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

My experience with Windows 10 over a one year period and suggestions for smooth operation
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

738 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