Autofill Formula to Pre-Existing Row

Jeremy Myers

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

Jeremy,

 

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.

 

Craig

 

 

Jeremy Myers

Craig,

 

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.

 

jeremy

J. Craig Williams

Jeremy,

 

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:

 

http://help.smartsheet.com/customer/portal/articles/1641473

 

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

 

Hope this helps.

 

Craig