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.

Autofill Formula to Pre-Existing Row

Jeremy Myers
Jeremy Myers ✭✭✭✭✭
edited 12/09/19 in Archived 2015 Posts

On a pre-existing row of data, I want to have a formula autofill when I input new data., i.e. when an escrow closes for a client.


In Excel I would simply pre-populate the column by stuffing the formula below into row 1 and then dragging down to row 2,000 then locking the column.  Can I do something similar in Smartsheet?


IF(ISDATE(Escrow Close Date), (Sale Price) * (Commission Rate), ""



  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 12/27/15



    If I understand correctly, then yes you can.


    The formula would need the row number, like this for row 23

    use [] around column names with spaces, and close all parathesis.


    =IF(ISDATE([Escrow Close Date]23), ([Sale Price]23 * [Commission Rate]23), "")


    Once the formula is there, you can drag it (using the lower right corner like in Excel) or copy/paste it where you like.


    Hope this helps.





  • Jeremy Myers
    Jeremy Myers ✭✭✭✭✭



    I want to have an entire column called <gross commission earned> contain the formula at all times without relying on the autofill feature, dragging down a formula or copy / paste.


    Autofill is defeated when a new row is created if the source cells for the calcualtion are left blank.  In a real estate application example, I create a row with basic customer info but we don't yet have a sale so <sales price> and <commission rate> are initially blank.


    Once I have the hosue sold I go back and fill in the relevant data, then I want <sales price> * <commission rate> = <gross commission earned> to be calculated. 


    I am trying to idiot-proof the sheet for the agents, so an Autofill function that is active at all times on the column, then locking the column would be ideal.  


    That sounds like a product enhancement to autofill which I will submit.



  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    If there is a formula in a row above or below where the new row is added, the formula (and formatting) will be copied there. 

    I think it might do what you want now.


    Check this out:




    Just like other spreadsheet apps, if a user deletes something undesired, all bets are off.


    Hope this helps.



This discussion has been closed.