Autofill Formula to Pre-Existing Row

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), ""

Find more posts tagged with

Formulas

Comments

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.