Numbers get added decimals after being linked?

Cecilie

Hi all

I have a bit of an annoying issue with Smartsheet adding decimals to numbers after they're being linked - and these dosen't even match my current settings. Currently in our Intake sheet, we have a store number and postal code, which has no decimals. However, when these are being linked out to the sheets and beyond, they suddenly get a decimal.

I have tried looking into personal settings and column settings without much succes. My number format is set to the following, which the SQM manages to follow, but for some reason the postal code and the project number does not.

Anyone have any ideas?

Thank you for reading 😊

Genevieve P.

Hey @Cecilie

I've been able to reproduce what you're seeing with two specific settings:

1. My personal settings as Deutsch
2. Cell linking in a value in a Text/Number column into a Date type of column

Since date cells cannot house numbers, it translates the number into a Text value in order to display it. This is when the additional decimal is showing up incorrectly.

There are a few potential ways that I have found to resolve this:

1. Ensure the data is read as Text originally in the source sheet (you can add a ' in front of the value)
2. Link the value into a Text/Number column instead of Date (not a Primary column)
3. Use VALUE() to translate the text into a number in a different column
4. Use a Formula with a single {Reference} + "" instead of a cell link

Let me know if any of these will work for you!

Cheers,

Genevieve

Itai

Hey @Cecilie,

Have you tried choosing the column and using decrease decimal?

Cecilie

Hey @Itai

Yes, unfortunately it doesn't do anything; I'm guessing because the number is linked from another cell.

Genevieve P.

Hi @Cecile Moll

I agree with @Itai - you'll want to ensure all your columns have the same properties set. So, ensure the entire column is selected then adjust the decimal settings on both the original sheet and this destination sheet.

Cecilie

Hi @Genevieve P.

I tried selecting the entire column and change the decimals settings for both sheets, but nothing changed in the destination sheet. Even if I add decimals to my Intake sheet, the numbers in my destination sheet still follow the wrong formatting rules.

Eric Law

@Cecilie Have you tried imbedding the link in a =ROUND formula? That way the sheet will be able to edit the number on the sheet you are linking to.

Cecilie

@Eric Law I can't quite seem to get it to work. Since I don't want any decimals I left it at default, but nothing changed. I am not familiar with the fomula, so it might be my mistake.

Can you make an example of how a ROUND fomula would look in this situation of wanting no decimals?

KPH

@Cecilie

This is how to use the ROUND formula

It takes the 1.11 in column5 and rounds the data so there is a 1 in column6

You can replace everything in blue here with your formula including the reference to the other sheet.

=ROUND(..............................your formula................... ,0)

However, if I increase the decimal places on Column6 to be 2 decimal places it shows the zeroes so, sadly, this might not be a solution for you. But worth a shot!

I'll keep my fingers crossed for you.

Genevieve P.

Hey @Cecilie

I've been able to reproduce what you're seeing with two specific settings:

1. My personal settings as Deutsch
2. Cell linking in a value in a Text/Number column into a Date type of column

Since date cells cannot house numbers, it translates the number into a Text value in order to display it. This is when the additional decimal is showing up incorrectly.

There are a few potential ways that I have found to resolve this:

1. Ensure the data is read as Text originally in the source sheet (you can add a ' in front of the value)
2. Link the value into a Text/Number column instead of Date (not a Primary column)
3. Use VALUE() to translate the text into a number in a different column
4. Use a Formula with a single {Reference} + "" instead of a cell link

Let me know if any of these will work for you!

Cheers,

Genevieve

Cecilie

Hi @Genevieve P.

Yes exactly, I should have mentioned that my settings are non-American.

Thank you for the explanation. Since my destination column must be a date column, I have used an ' in front, and it worked perfectly for this situation 😊

Thank you very much for the time taken and the help!