How do I set up a "Unit" conversion system in EXCEL

[Tony Borg]

I have set up two lists in EXCEL with different units eg, Gallons, Litres,
Degrees Celsius, Degree Fahrenheit, Degrees Kelvin etc. etc. The two lists
are identical. I want users to be able to select a "From Unit" from one list
and a "To Unit" from the other list then, enter a figure in one cell of the
worksheet to convert it from one unit to the other eg. Gallons to Litres. In
the column next to the Units I have the requisite symbols eg, "gal" , "L" etc.
Any ideas would be appreciated.
Regards,

 

[NickHK]

Tony,
The cheat way would be to have the expected abbreviations (see the Help) for
these units in the adjacent column and call the CONVERT worksheet functions
with quantities.

NickHK

 

[Tom Ogilvy]

Just to add (from the help on convert)

If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

 

[Tony Borg]

Nick,
Thankl you for your answer to my query. I had already done what you
suggested (I think). The lists I mentioned are in drop down lists as provided
in EXCEL. The abbreviations are in the adjacent columns to the lists. What I
don't know how to do is for example I pick Gallons in the "From List" and
Litres from the "To List" then when I use the CONVERT Function I need EXCEL
to automatically use the adjacent cells information which contain "gal" and
"L" respectively. If I manually input the adjacent cells' abbreviation in the
convert function it works allright! So it's the automated bit is what I am
having difficulty with.
Regards
Tony Borg

 

[Tony Borg]

Tom thank you for your reply. I believe I did what Nick suggested and the
CONVERT function works OK. My real difficulty is that I do not know how to
get EXCEL to automatically insert the abbreviations from the columns adjacent
to the "From Units" and the "To Units" into the convert Function.
Regards
Tony Borg

 

[NickHK]

Tony,
Assuming your From and To lists have their .LinkedCell property set, then
you can do a VLookUp of the correct abbreviation for the desired units.
If the .LinkedCell(s) are A10 and C10
A11=VLookUp(A10,A1:B9,2,True) ;C11=VLookUp(C10,C19,2,True)
Then, with the quantity to convert in A12,
C12=CONVERT(A12,A11,C11)

You get the idea ?

NickHK