If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Find the first blank row in a cell
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 |
#2
|
|||
|
|||
Find the first blank row in a cell
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 |
#3
|
|||
|
|||
Find the first blank row in a cell
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 |
#5
|
|||
|
|||
Find the first blank row in a cell
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 |
#6
|
|||
|
|||
Find the first blank row in a cell
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 |
#7
|
|||
|
|||
Find the first blank row in a cell
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 |
Thread Tools | |
Display Modes | |
|
|