Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Show days left in Smartsheet

sonnenallee
edited 12/09/19 in Archived 2017 Posts

Hi,

 

I'm total newbie in Excel functions. I want to show the days left from a date. So I have the follow formula which works:

=[Column-Name]23 - TODAY()

This only works for row 23 of course. But I just want the current row in it so I can use the same formula in every row. So In row 8 it uses the date from row 8, in row 9 from row 9 etc.

How do I make this happen?

Thank you

Tags:

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    If you 1) select the cell, and 2) grab and drag the lower right-hand corner down to the last row it will renumber the row 23 accordingly. Then reselect row 23 and do the same thing upward to row 1. 

    That should do the trick. 

     

  • I believe this is a lot easier than you think.  Simply single click on the cell, copy, select the rows you want, and paste the cell into the rows.  The row address number will change automatically.

    **Do not open the cell (double click) and grab the text, and then paste.  That will prevent the formula from changing.

     

    Occasionally you may not want the row address to change.  In this case you would add a $.  With the dollar sign added, this formula will not change the row address number when copied.

    =[Column-Name]$23 - Today()

    Good Luck

  • Thank you both of you, this was really very easy! Worked well.

    Is there a simple way to drag the righ-hand corner down infinitely, or at least until row 1000 or so? We only use around 100 rows but we delete and add new rows everyday.

    Thank you

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Smartsheet has a useful auto-fill feature. https://www.smartsheet.com/videos/autofill-formulas

    If you add new rows between the rows your formula should appear in that new row. If you drag all the way to the bottom of the sheet and then delete any blank rows from the bottom, and new rows are added via a form will appear with the new formula. Smartsheet will automatically readd 10 or so rows, but that's okay - once you've cleared any blank ones, any new rows will contain your formula automatically. 

This discussion has been closed.