1. ## Metric vs Stnd, Currency in Global Spreadsheet (2K

All,

I have a spreadsheet that calculates performance data for various products. The audience is global and wants to see the information in their default unit system. Is there a good way to calculate and display in either metric or standard units depending on where the recipient is viewing the spreadsheet? The workbook is fairly complex, containing specifications on many different products. The user selects the products they wish to see via radio buttons on the home sheet and the performance characteristics are then calculated.

My challenge is that some viewers want metric/euro data, some metric/pound, some metric/dollar, some standard/dollar, etc. To complicate this even further, not all products are available in all locations around the globe. I can envision an intorductory page where the user would select the measurement system, currency system, and global location. The global location is fairly straightforward in terms of product availablility. If the products were not available, various members of the list would be greyed out.

Where I struggle is with the units of measurement. In the past, I maintained separate workbooks for metric/dollar, metric/pound, metric/euro, and standard/dollar. Now, the client wants a "one-size-fits-all" spreadsheet. Does anyone have suggestions on how this might be elegantly accomplished?

2. ## Re: Metric vs Stnd, Currency in Global Spreadsheet (2K

See if the attached workbook can be adapted for your use. I have left everything visible, in the definitive version you would hide the worksheet imaginatively named "Hidden". This worksheet contains lists and conversion factors. The worksheet named "Display" would be protected so that the user can't edit the formulas.

3. ## Re: Metric vs Stnd, Currency in Global Spreadsheet

Hans,

Thanks for the response. I am still working on it. I have a question about the properties of the combo box you use on the "display" sheet.

When I open the properties box for the "length" combo box, I see two properties that refer to linked cells. The first is "linked cell" pointing to J2 and the second is "linked range" pointing to E2:E3. I unterstand the first one - the cell that will display the value chosen. Does the second one, the "range" property refer to the list of values displayed in the combo box?

Are there on-line resources that I should be studying to help me understand how to use these tools?

Douglas

4. ## Re: Metric vs Stnd, Currency in Global Spreadsheet

You are correct. LinkedCell is the addres of the cell that holds the value selected in the dropdown list, and ListFillRange is the address of the range of cells used to populate the dropdown list.

- Activate the Visual Basic Editor (Alt+F11)
- Activate the Object Browser (F2)
- Type the term you want to know more about, for example LinkedCell or ComboBox.
- Press Enter.
- Select an item in the list of search results.
- Press F1 to open the help file.
- You can use the links in the help file to find out more.

5. ## Re: Metric vs Stnd, Currency in Global Spreadsheet

Thanks, Hans. Now a follow-up question.

Can I change part of a text string as a result of the currency choice? Example would be a cell label saying "\$ per lb." followed by a cell formatted for USD currency. If the user selects Euros and metric measurements, I would like the text to read "Euros per Kilo" and the cell to be formatted to Euro currency. Can this be done?

TIA, Douglas

6. ## Re: Metric vs Stnd, Currency in Global Spreadsheet

You can concatenate the linked cells of the combo boxes with fixed text. In the workbook I attached higher up in this thread, you could use

<code>=Hidden!J4 & "s per " & Hidden!J2</code>

to get (for example) "US Dollars per foot" or "Euros per meter". If you want to use texts different from the linked cells, you need to construct more lookup tables, and use VLOOKUP to find the correct text, cf. some of the formulas in the sample workbook.

7. ## Re: Metric vs Stnd, Currency in Global Spreadsheet

I think I understand that one.

How about changing the format of a cell that shows data? Can a cell that is formatted to show USD currency be changed to show Euro currency as a result of a user selection from a drop-down box?

Douglas

8. ## Re: Metric vs Stnd, Currency in Global Spreadsheet

You would have to change the format via a macro not a formula if you wanted it be the number.

If you didn't mind converting to a string you could use the text function:
=Text(A1,B1)

where B1 is a formula that would get the format based on the dropdown.

This would no longer be number!
Steve

9. ## Re: Metric vs Stnd, Currency in Global Spreadsheet

I have decided to remove the formatting for cells that display currency and to simply change the text labels adjacent to the cell. So that should solve that problem.

My "standard vs. metric" selection is more complicated than I had originally anticipated. I had hoped that I could have the user simply indicate a preference for either standard or metric units of measurement (one choice) and then select and include the appropriate mulitipliers in the various formulas. But, I have a lot of different units to contend with. I have liquid volumes (gallons and ounces to liters), distance (feet and inches to centimeters), speeds, areas, cost per unit volume, etc.

Any suggestions on how I might approach this?

Douglas

10. ## Re: Metric vs Stnd, Currency in Global Spreadsheet

It's a lot of work, but it could be done using the approach from the workbook I attached higher up in this thread. The basic idea is to store all values in one set of units. You could use one combo box to switch the display between unit sets (probably more than just metric vs non-metric, for instance a UK gallon is different from a US gallon).

11. ## Re: Metric vs Stnd, Currency in Global Spreadsheet

All,

I have made progress and have solved most of the problems. One that remains is where I have a text label that defines an adjacent data cell. As previously mentioned, I have removed the specific formatting of the data cells and am now relying on the text to inform the user.

I have given names to the various metric and standard data types that I am using and if that is the only thing that has to appear in the text cell, there is no problem (ex: liter vs. gallon). I simply insert a formula that calls the name (ex: "=L_volume" supplies either the metric or standard unit for volume depending on which form of units the user has selected. But when I have a mix of regular text and a "called name" in the same cell, I have so far, failed to make it work.

Example would be: (sq.ft. / hr.) where the "sq.ft." part would be supplied by a "name" and the "/ hr." would be plain text. Can anyone give me a hint here?

Douglas

12. ## Re: Metric vs Stnd, Currency in Global Spreadsheet

<post#=437476>post 437476</post#> gives an example using cell addresses; you can concatenate with defined names as well. Say that your area unit is in a cell named L_area. The formula

<code>=L_Area&" / hr."</code>

will display "sq.ft. / hr." if L_Area contains "sq.ft". You can combine several named cells in one formula, if necessary: say that L_Volume contains "gallon" and L_Currency contains "dollar". The formula

<code>=L_Currency&" / "&L_Volume</code>

will display "dollar / gallon".

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•