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.

Automatic formula fill

Options
Thane Knutson
Thane Knutson ✭✭
edited 12/09/19 in Archived 2016 Posts

It says in the smartsheet help files that inserted blank rows should automatically fill formulas if the new row is between two contiguous formulas...  For most of my sheet this is true.  However, I have one very long formula that identifies a month based on the date and it seems to stump the autofill.  Is this intentional or a bug?

 

example:

=IF(MONTH(START1) = 1, "JANUARY", IF(MONTH(START1) = 2, "FEBRUARY", IF(MONTH(START1) = 3, "MARCH", IF(MONTH(START1) = 4, "APRIL", IF(MONTH(START1) = 5, "MAY", IF(MONTH(START1) = 6, "JUNE", IF(MONTH(START1) = 7, "JULY", IF(MONTH(START1) = 8, "AUGUST", IF(MONTH(START1) = 9, "SEPTEMBER", IF(MONTH(START1) = 10, "OCTOBER", IF(MONTH(START1) = 11, "NOVEMBER", IF(MONTH(START1) = 12, "DECEMBER"))))))))))))

Comments

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

    Thane,

     

    It should not matter what the formula is, as long as it works in the rows above or below (depending on where you add the new row)

     

    Auto-fill look around (up and down) to see if there is something (formula or formatting) to use. Are you adding from a WebForm? Are there blank rows nearby?

     

    There's nothing wrong with your formula (assuming your column name is START and not Start).

     

    Craig

     

  • Thane Knutson
    Options

    Capitalization is ok...  The new rows are normally inserted in the middle of the sheet, only one at a time. Other simple formulas columns fill in just fine.

  • Travis
    Travis Employee
    Options

    Has this formula autofilled on some sections of the sheet or has it never worked?

     

    Check to see if you have predecessors in the parent row of the section where you are inserting the new row. If the parent row has predecessors, formulas will not autofill.

     

    Also, make sure you are inserting or using a NEW row (a row that has never contained data). Used rows (even if they are blank) will not autofill. 

  • jonathan.hunter253751
    edited 09/19/18
    Options

    Will auto fill work to change a single criteria over and entire sheet?

    =COUNTIFS({D004 Counterbalance Operator Evaluation Tr Range 1}, <=DATE(2018, 4, 7), {D004 Counterbalance Operator Evaluation Tr Range 1}, >=DATE(2018, 4, 1), {D004 Counterbalance Operator Evaluation Tr Range 2}, "Joe Fresh")

    I am using the above formula that feeds from another spreadsheet to count the amount of entries within a certain week if completed by "Joe Fresh" 

    The sheet contains columns counting from 3 different sheets all with similiar formula above. The rows contain the counts from those 3 sheets for each week. Week 1, Week 2, Week 3, etc. I have set up sheets that take information for each person. EG. 1 Sheet is for Joe Fresh another is for Betty Smith. I am trying to copy the original master sheet and then change the entire criteria for the sheet by changing the name. "Joe Fresh" to "Betty Smith". I was hoping smart sheet would recognize this change in one cell and apply it to all the formulas. 

    Now each cell contains a different >< between dates formula and from different sheets so this may be why i haven't found a work around and am now changing the criteria in each cell on each sheet seperately. I have to create 15 different sheets so this is going to be a lot of individual formula changes.

    Also i have to do the date changes individually for each formula because when i copied the column of formulas to another row and tried to change the sheet location it changed all the cells. It doesn't seem like smartsheet learns dates like in excel when you can drag a date formula using 3 cells down and column and have it figure out the date sequence.

     

This discussion has been closed.