Currency and formulas and cell references.

Jim B

Hello,

How do I get the currency symbol to pull through in a cell reference??

I have a sheet which contains multiple cost lines in multiple currencies in the Estimate column. Like so:

£40

$60

etc.

If I want to reference that column elsewhere in the sheet e.g. =Estimate@row/2 how can I get the currency sign pulled through?

If I define the currency in the Estimate column (or cell) using the Currency dropdown- I get no currency symbol in the referencing cell, just the value (40, 60)

If I apply 'No Currency' from the dropdown to the Estimate column and manually input £40 or $60 - I get no currency symbol in the referencing cell, just the value (40,60)

The only way to get the symbol is manually select the appropriate Currency from the dropdown in every individual cell right the way across the sheet where I am expecting £ $. This is going to be c.5 or 6 cells! If there's user error and we have £ in one and $ in another, well....you can imagine the consequences.

Am I doing something wrong?

Is there a smarter way to deal with it where put maybe a currency dropdown in another field and then use a formula to convert the requisite cells into £/$ depending on the value in the dropdown.

Hope someone can help.

Thanks,

Jim

Paul Newcome

You can click on a row number and change the currency symbol across all columns at the same time for that entire row.

Jim B

Thanks, Paul.

Unfortunately that changes all the other numbers in the row to currencies too. Ie Production Numbers, PO Numbers, Deal Numbers, Invoice Numbers etc. etc. They all end up at currencies...Not what I want.

The best solve I have at the moment is making additional columns for formulas, calculating these off a non-currency number, hiding the columns (for usability) and concatenating the result and the relevant £/$ symbol in the fields where I need to see the currency symbols. It's very fiddly.

Also because once they are concatenated I can't format the commas or the decimals (e.g.) £100,000,000.00 it's not so readable.

Jim

Jim B

Does anyone else have any power tips for when it comes to using multiple currencies and cell references?

I'm at a point with my users where I'm just going to have to tell them they have to remember to manually define the currency for every single cell where it is required. This is a lot of cells.

I thought to help them along I could write in some conditional formatting that would flag where a cell doesn't have the requisite currency symbol at the front of the number based off a currency dropdown option in each row

Problem is - Smartsheet doesn't 'see' the currency symbol at the front of a number so I have no way of telling people 'Hey it's meant to be all £££ in this row but you've got a $ here - pay attention!'