VLOOKUP conversion formula help

Thank you!

I have updated the question in this posting to make it a bit more clear as well as attached the excel file. All help is GREATLY appreciated!!!

I don't know exactly what those conversions are etc, but if I were to just look at the vlookup, it's giving me the correct value.

i.e, EUR/M3 will always shows as $0.43

Originally Posted by dluhut

I don't know exactly what those conversions are etc, but if I were to just look at the vlookup, it's giving me the correct value.

i.e, EUR/M3 will always shows as $0.43

Yes the formula is correct, but when you copy it down on first sheet the answer should change for each record because the value in Cost is different in each record. When you past it down the answer is always same as from copied cell. An easy way to test is to change the cCost value in column B and the value in column D should only change for the record changed.

Wait, are you saying that when you copied down, you always get $0.43?

If that's the case, maybe your calculation set to Manual.

All very confusing:

Change "Cost" in Name

100 in B2

Result is

E2 in "Currency_Uom" now 0.48453

D2 in name is the above value as is D5.

If D5 in "NAME is now 200 ..

then E5 in "Currency_Uom" is now 200! which given the formula =($C5/$D5)*Name!B5 is correct.

The formulas in "Currency_Uom" column E are NOT static as they relate to formulae in "Name"

In summary I am not sure what you require!!!!

Originally Posted by JohnTopley

All very confusing:

Change "Cost" in Name

100 in B2

Result is

E2 in "Currency_Uom" now 0.48453

D2 in name is the above value as is D5.

If D5 in "NAME is now 200 ..

then E5 in "Currency_Uom" is now 200! which given the formula =($C5/$D5)*Name!B5 is correct.

The formulas in "Currency_Uom" column E are NOT static as they relate to formulae in "Name"

In summary I am not sure what you require!!!!

I know it's confusing. So here is more detail on what the values are: Sheet "Name" fields are defined as follows...


Sheet "Name"
Cost = numeric value
Currency/UoM = the currency and unit of measue of value in Cost
USD = is the US dollar/US gallon based on calculation that is used to convert the value in Cost to USD/USG

Example = record 2 in "Name" reads 89 Euros per Metric Ton is converted to $0.43 per Gallon. The calculation for that conversion is in column E of sheet "Currency_UoM" where the Curr_UoM = VLOOKUP


Sheet "Currency_UoM"
Curr_UoM = short name for currency and unit of measure value
FX_Rate = Foreign exchange rate base on currency
ConvCalc = number used to perform conversion
USD/USG = converted value of record of focus in column B of Sheet "Name"


When the formula in D2 in sheet "Name" is copied and pasted down that column, it should calculate based on corresponding value in column B of that row

The formulas in sheet "Currency_UoM" are static EXCEPT for their reference back to column B in sheet "Name"


As seen in the new excel file attached the copied cells in column D of sheet "Name" all have the value of $0.91 when the value being converted in column B changes.

Thank you very much for your time!!!