VLOOKUP - return actual formula

  • Thread starter Thread starter kvc
  • Start date Start date
K

kvc

hi, i have 2 worksheets.. using a vlookup to return data from
worksheet A to worksheet B.

Worksheet A has some formulas in the cells, and when I do a vlookup to
worksheet B it only returns the values, not formulas... is there a way
to show the formulas from worksheet A onto worksheet B?

thanks!
 
Not by regular means.......it could be done with multiple VLOOKUPS or VBA but
even then it would be misleading, as the formulas would refer to cells
located on Sheet A.

Vaya con Dios,
Chuck, CABGx3
 
That's what lookups do............return values.

To see the formula from another sheet you could use a UDF

Function ShowFormula(Cell)
Application.Volatile
ShowFormula = "No Formula"
If Cell.HasFormula Then ShowFormula = Cell.Formula
End Function

On sheetA enter in a cell =ShowFormula(SheetB!A1)


Gord Dibben MS Excel MVP
 
You can do this using XL 4.0 macros.

Say Sheet1 has your datalist, from A1 to B20.
Column A is names,
Column B is formulas that calculate values pertaining to those names in A.

Sheet2 has your Vlookup formula, say in A1, with the name to lookup in G1.
=VLOOKUP(G1,Sheet1!A1:B20,2,0)

So, with the value returned to A1 of Sheet2, say in B1 of Sheet2 we want to
display the formula that produced that value.

We're now going to create a "named formula" to retrieve that formula.

From the Menu Bar:
<Insert> <Name> <Define>
In the "Names In Workbook" box, type in the name we're assigning to this
formula,
let's say we'll name it "formula" (no quotes).
Then, in the "Refers To" box, *change* whatever's there to this:

=GET.CELL(6,INDIRECT(ADDRESS(MATCH(Sheet2!$G$1,Sheet1!$A$1:$A$20,0),2,4,,"Sheet1")))

Then click on <OK>

NOW, in B1 of Sheet2, simply type in
=formula
And you'll see the formula for the value displayed in A1 of Sheet2.

Now a CAVEAT:

This can safely be used in versions of XL, from XL02 onward.

Earlier versions *WILL* CRASH*, causing the loss of all unsaved work, when
attempting to copy these formulas to *other* WBs.


Can be used safely in earlier versions as long as copying is restricted to
sheets within the existing WB.
 
Back
Top