Skip to content
  • Try Smartsheet

    • Smartsheet Basics

    Smartsheet Basics

    Ask questions about the core Smartsheet application: Sheets, Forms, Reports, Dashboards, and more.

    Numbers get added decimals after being linked?

    ✭✭✭
    in Smartsheet Basics

    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.

    nummer.PNG
    Udklip.PNG


    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.

    number format.PNG

    Anyone have any ideas?

    Thank you for reading 😊

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • Employee
      Answer ✓

      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

      Need more information? 👀 | Help and Learning Center

      こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭✭

      Hey @Cecilie,

      Have you tried choosing the column and using decrease decimal?

      image.png


      Itai Perez

      If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

      https://www.linkedin.com/in/itai-perez/

      · Share on FacebookShare on Twitter
    • ✭✭✭

      Hey @Itai

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

      · Share on FacebookShare on Twitter
    • Employee

      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.

      Need more information? 👀 | Help and Learning Center

      こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

      · Share on FacebookShare on Twitter
    • ✭✭✭

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      @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.

      · Share on FacebookShare on Twitter
    • ✭✭✭

      @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?

      · Share on FacebookShare on Twitter
    • Community Champion

      @Cecilie

      This is how to use the ROUND formula

      image.png

      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!

      image.png

      I'll keep my fingers crossed for you.

      · Share on FacebookShare on Twitter
    • Employee
      Answer ✓

      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

      Need more information? 👀 | Help and Learning Center

      こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

      · Share on FacebookShare on Twitter
    • ✭✭✭

      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!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Quick Links

    Categories

    Trending in Smartsheet Basics

    Help shape the future of Smartsheet.
    Share your ideas and feature requests.

     Watch video