Archived 2015 Posts
Archived 2015 Posts
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
