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

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

On Nov 6, 3:48*pm, Chris Anderson <Chris
wrote:
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

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


"Chris Anderson" <Chris wrote in message
...
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

Sorry, forgot to fix the reference:

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


--
HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Chris Anderson" <Chris wrote in message
...
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

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" wrote:

Sorry, forgot to fix the reference:

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


--
HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Chris Anderson" <Chris wrote in message
...
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 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

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" wrote in message
...
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" wrote:

Sorry, forgot to fix the reference:

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


--
HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Chris Anderson" <Chris wrote in message
...
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 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

That worked great! Thanks.

"Bernie Deitrick" wrote:

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" wrote in message
...
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" wrote:

Sorry, forgot to fix the reference:

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


--
HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Chris Anderson" <Chris wrote in message
...
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 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