combobox-calculation (EXcel 2003 SP3)

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

combobox-calculation (EXcel 2003 SP3)

Post by steveh »

Morning all

Many of the youngsters in our office do not understand proper measurements and use that metric malarkey ( :grin: ) so I am putting together a conversion chart of the common ones that we use in the office. Instead of just having a great long list I thought it would be better if I used a combobox to select the measurements but I do not know how to then bring in the right result.

I have attached an abridged version of what I am doing with the listed measurements and then what I am trying to do with a combo. Or better still could this be done on a user form.

Any insights please
You do not have the required permissions to view the files attached to this post.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

User avatar
mbarron
2StarLounger
Posts: 112
Joined: 25 Jan 2010, 20:19

Re: combobox-calculation (EXcel 2003 SP3)

Post by mbarron »

Something like the attached?
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: combobox-calculation (EXcel 2003 SP3)

Post by HansV »

I'd let the user enter the "from" and "to" units in separate cells. You can use Data | Validation... to present dropdown lists in the "from" and "to" cells.
You can then use the CONVERT function to convert the number.
Say the value is in A1, the "from" unit in B1 and the "to" unit in B3. The formula =CONVERT(A1,B1,C1) will return the converted value.

In Excel 2003 and before, you need to have the Analysis ToolPak function installed for the CONVERT function to be available.
See CONVERT Function.
Best wishes,
Hans

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Re: combobox-calculation (EXcel 2003 SP3)

Post by steveh »

Thanks Mike and Hans

Both of the solutions are very good. I am trying to understand the CONVERT function though but keep getting the #NAME? error, I have double checked that I have the Analysis Toolpack installed but I still get the error?
You do not have the required permissions to view the files attached to this post.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: combobox-calculation (EXcel 2003 SP3)

Post by HansV »

You have to specify the "from" and "to" units in a specific way - the article I posted a link to in my previous reply lists the units you can use.
For metres, use m.
For feet, use ft.
Best wishes,
Hans

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Re: combobox-calculation (EXcel 2003 SP3)

Post by steveh »

:blush: Oops

Did'nt realise I thought it was for converting other things, I see now

Thanks again
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin