Find the first blank row in a cell

[Chris Anderson]

I have a row of cells some of which are populated with an "x". I am looking
for a function that will tell me the first blank cell in a row. For example,
see below. I want to put a formula in column B that says, "find the first
blank cell in the row and return the task number. In cell B2 the return
should be E1 (or "3" indicating that John is currently on task 3). In cell
B3 the return should be D1 (or "2" indicating that Joe is on task 2).

A B C D E F
1 Task 1 2 3 4
2 John X X
3 Joe X
4 Martha X X X

The actual spreadsheet that I am working with is far more complicated but
this is generally what I am trying to accomplish. Can anyone help?

Thanks

 

[Pete_UK]

You could use this in B2:

=IF(COUNTIF(C2:F2,"X")=4,"completed",COUNTIF(C2:F2,"X")+1)

Then copy down as required.

Hope this helps.

Pete

 

[Bernie Deitrick]

Chris,

=INDEX(C1:F1,COUNTIF(C2:F2,"X"))

Or simply

=COUNTIF(C2:F2,"X")
(works if your tasks really are labeled 1, 2,3, 4, etc.)

copied down to other cells in column B.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Sorry, forgot to fix the reference:

=INDEX(C$1:F$1,COUNTIF(C2:F2,"X"))

 

[Chris Anderson]

Thanks Bernie. I am actually looking for a way to tell me where the first
blank cell in a row is. I have revised the example below such that John has
completed tasks 1, 2, and 4. I would like to know what I formula I can use
that will tell me that the next task John needs to complete is task 3.
Thanks for the help.

Chris.

 

[Bernie Deitrick]

Chris,

One way: Array enter this (enter using Ctrl-Shift-Enter)

=INDEX($C$1:$F$1,MATCH(TRUE,ISBLANK(C2:F2),FALSE))

HTH,
Bernie
MS Excel MVP

 

[Chris Anderson]

That worked great! Thanks.

Chris,

One way: Array enter this (enter using Ctrl-Shift-Enter)

=INDEX($C$1:$F$1,MATCH(TRUE,ISBLANK(C2:F2),FALSE))

HTH,
Bernie
MS Excel MVP