Formulas within IFs. In a col, ignoring blank cells above until you reach a number then +1

[MushroomFace]

Please see attached.

Job Log
I am looking for a formula to go into column B. If A1 equals Accept, then 1, if not #blank and so on. However the formula will need to go up in sequential order by recognising the blanks in between the rows. So A1+1,would not work, see B6 on spreadsheet, the above cell is blank and this would result in 1. Is this possible?

Column E will be sequential but if the number is not in column B yet, for example 4, it will show up blank. It may seem lazy but it will be used on a large database but I am struggling with this part! Boo.

[Richard Buttrey]

Hi,

One way

Please Login or Register  to view this content.

[FDibbins]

try this for the 1st 1, copied down...
=IF(A3="accept",MAX($D$2:D2)+1,"")

[MushroomFace]

Thank you both.

Richard I have not used the COUNTIF formula before, looks like it could be handy! Thanks.

Anyone else for the second part?

[Richard Buttrey]

Hi,

Re the column E question.

I can think of two options. Either to use a conditional format. e.g. in E3 and applied to the whole of column E

Please Login or Register  to view this content.

and set the font colour to be the same as the cell background colour.

or In E3 copied down

Please Login or Register  to view this content.

[mehmetcik]

Try this:-

[MushroomFace]

Thanks, I prefer the method used on Mehmetcik's example, as column E is looking up the value in B. Columns F onwards will be formulated depending on E - which I didn't mention earlier.

Thanks for your quick response.