Problem with Formula when inserting a row

Ramsay Zaki

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?

 

Greg Gates

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

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

Ramsay Zaki

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

Ramsay Zaki

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

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

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....