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.

Problem with Formula when inserting a row

Options
Ramsay Zaki
Ramsay Zaki ✭✭✭✭✭✭
edited 12/09/19 in Archived 2016 Posts

I'm not sure how to handle the following situation... my data looks as follows:

 

 

The formula in Status2 (and filled down) reads as follows:

=IF([Act Finish Date]2 = "", "Open", IF(AND([Act Finish Date]3 = "", [Act Finish Date]2 <> ""), "Closed, Last Event", "Closed"))

 

In other words, the formula is checking not only if a row is Open or Closed but also determining which was the last closed event.

 

All of that works great until I insert a row between rows 5 and 6. Two things go wrong...

 

  1. 1. The formula in Status5 no longer looks at the next row down. Instead it keeps looking at the row it was looking at before I inserted the new row (which is now row 7... it skips the newly inserted Row 6).
  2.  
  3. 2. The newly inserted row doesn't automatically fill down (presumably because of problem #1)

 

 

Any thoughts on how to get the newly inserted row to automatically get the formula and also have Row 5 always look at the "next" row even if I insert a new row below it?

 

sample.png

sample2.png

Comments

  • Greg Gates
    Greg Gates ✭✭✭✭✭
    Options

    This is a really convoluted way to do this, which makes me think there must be a better way. If someone finds it, definitely let me know! Here's the best I could come up with, taking advantage of the new INDEX function.

     

    First, I'm going to assume your Row # column is an actual column. If that's the case, you could do something like:

     

    =IF(INDEX([Col 1]:[Col 1], [Row #]1 + 1, 1) = "", "Next Row Blank", "OK")

     

    So rather than referencing the cell directly, we're referencing "the cell one row down from me." Since we're no longer referencing a cell outside the current row, inserting a new row won't affect this formula.

     

    I hope that makes sense! Let me know if that doesn't solve your issue.

  • Ramsay Zaki
    Ramsay Zaki ✭✭✭✭✭✭
    Options

    I have updated my original post... I tried to simplify my situation too much. Now it refelcts my real situation.

  • Ramsay Zaki
    Ramsay Zaki ✭✭✭✭✭✭
    Options

    Thanks Greg... I understand what you mean. I'll give it a try later today.

  • Ramsay Zaki
    Ramsay Zaki ✭✭✭✭✭✭
    Options

    Greag - I tried what you suggested but the Row # part doesn't work right. When I add a new row, the Row # columns is blank. If I try to add the system row # data type, the new row # is not in order so the calculation is off (it takes the next available row # for the entire sheet). :-(

  • Greg Gates
    Greg Gates ✭✭✭✭✭
    Options

    Could you set your Row # column to be the "Auto-Number" column type (or add this column in if you don't have it already)? You could even hide this column once you add it in if you don't want to see it.

  • Ramsay Zaki
    Ramsay Zaki ✭✭✭✭✭✭
    Options

    That's exactly what I did (set the Row# to AutoNumber). But when you add a new row in the middle it doesn't put the next # in order... it puts the next # from the entire sheet. So, my numbering looks like this:

     

    1

    2

    3

    52

    4

    5

    etc....

This discussion has been closed.