Vlookup copying row FORMULA NOT VALUES

[JonBer]

Hi All
I have a problem with VLOOKUP copying FORMULA not VALUES
Attached is an excel book named EXCEL_FORUM.xlsx

Sheet 1 is the raw material pricing, Sheet 2 is finished product and Sheet 3 is an order, extracted from finished items in Sheet 2
I need to enter the finished product code in sheet 3, and copy the entire result row from Sheet 2 into the order (Sheet 3). NOT VALUES but Sheet2 FORMULA

The reason is that the quantities of raw material items making up the finished product in the order (Sheet 3) might need to be amended.
As can be seen from Sheet 3, the YELLOW shows the value from Sheet 2, whereas I need the green which shows the formula from Sheet 2.
In real life, Sheet 1 and Sheet 2 are separate books, but the principle is the same

[ChemistB]

When I open up your workbook, I see exactly the same values in the green and yellow cells. Do you maybe have your calculations set to "manual" (found on Formulas Tab of ribbon)?

[JonBer]

I am not sure if my problem is being understood.

In Sheet3, if I change the quantity value (column d), it will not change the price value in the yellow column, but will change the price value in the green column.
That is because when I brought the vlookup to Sheet 3, it did not bring the actual formula from Sheet 2 (which has links to Sheet 1)

If I did a simple copy and paste (as function) a row from Sheet 2, iT would copy the formula with links to Sheet 1, which is what I require.

Calculations are set to automatic.

[AliGW]

This is what you need to combine the two:

=IFERROR($D4*VLOOKUP($A4,Sheet2!A:J,5,FALSE),"")

[JonBer]

Hi All and thanks for the responses

Maybe I am not clear enough.
The cell in SHEET 2 has a formula relating to SHEET 1- That is the same formula I require to bring into SHEET 3
When I bring it across TO SHEET 3, I want to retain the FORMULA in SHEET 2 which references to SHEET 1
ie
SHEET 2 (cell E3)
=$D3*Sheet1!$B$3
And NOT
=IFERROR(VLOOKUP(A4,Sheet2!A:J,5,FALSE),"") (this will only give me the static value in SHEET 2) and not allow me to change the quantity of say hinges on order (SHEET 3)
In other words, I do NOT want the value of the cell in SHEET 2 But the Formula in SHEET 2 relating to SHEET 1

TKS

Jonathan

[kvsrinivasamurthy]

IN E4 of Sheet2 then copied to other columns

=D3*VLOOKUP(D$1,Sheet1!$A$3:$B$5,2,0)

IN E3 of Sheet3 then copied to other columns

=D4*VLOOKUP(D$1,Sheet1!$A$3:$B$5,2,0)