Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
macro to find the last blank cell in col. A | Excel Discussion (Misc queries) | |||
How do I find the next blank cell in a range? | Excel Worksheet Functions | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
IF function which can find a blank cell | Excel Worksheet Functions | |||
find the first and last non blank cell in a row | Excel Discussion (Misc queries) |