Display formula in another cell
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...
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Will "PriceList!F4" be the result of a specifi query, like the cell that contains a search value?
regards, barry
regards, barry
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
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
Formula -> name manage -> new --> Price --> =PriceList!F4
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:
=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
CellFormulaQ28308825.xlsm