Need help adding '1' to cell from cell above

[lorne17]

Hello there,

I am trying to get a CA log going and we have our RFI Number in column B. The formula we have is: =If(D22="","",B21+1). Basically if a date is inputted into column D, it will look at the row above and add 1 to it. I.E. If RFI 007 comes in, it will look above at 006 and add one automatically. That's working fine.

However sometimes we get requests that are not an RFI (Request for Information). So in column M the heading is: "Not an RFI?". If it's not an RFI, we put an X in that Cell. So now if there's an X in column M, i want B to not add a number and look to the next row above it that has a number in that cell. So Row 6 is RFI 006. But Row 7 is not a formal RFI, so it's blank. Therefore when Row 8 gets a date in column D then Row B reflects 007.

Does that make sense? I have attached an example of what I'm trying to accomplish.

Thanks,
Lorne

[amy_d2]

hi

Try this in B4 & drag down

Please Login or Register  to view this content.

[Pete_UK]

Put this in B3:

=IF(OR(D3="",M3="X"),"",MAX(B$2:B2)+1)

then copy down.

Hope this helps.

Pete

[lorne17]

Both options worked lovely.

Thank you!

[tacape]

Example RFI help.xlsx

=IF(Table1[[#This Row],[Column12]]="x","", IF(Table1[[#This Row],[Column12]]="",COUNT($B$3:B18)+1))