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