find first blank cell in a row

[Dwayne]

I need to create a formula that will enter a value based
on the location of the first blank cell in a row.

I am tracking the progress of projects in my company and
have set up a spreadheet as follows. My goal is to have
the current status automatically entered as I update the
project.


STATUS PART# PROTOTYPE PRODUCTION SHIPPING COMPLETE

production 123 X n/a
complete 124 X X X X
shipping 125 X X
prototype 126 n/a n/a



In the example, I want to create a formula in the STATUS
column that will automatically input the column heading of
the current status for each part.

An "X" in the table indicates the completion of that
stage, so for PART# 123, the PROTOTYPE stage is complete
and the current stage is PRODUCTION so "production" is
entered in the STATUS column for this part.

The only exception is if the part is marked COMPLETE, then
the STATUS needs to be marked "complete".

Thanks for your help,
Dwayne.

 

[Bob Phillips]

Dwayne,

This should do it

=IF(NOT(ISNA(MATCH(TRUE,ISBLANK(C3:F3),0))),INDEX($C$1:$G$1,1,MATCH(TRUE,ISB
LANK(C3:F3),0)),"")

its an array formula, so Ctrl-Shift-Enter rather than Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Dwayne,
Another option is a set of imbedded IF statements:

=IF(F2="X","COMPLETE",IF(E2="X","COMPLETE",IF(D2="X","SHIPPING",IF(C2="X","PRODUCTION","PROTOTYPE"))))

This checks the stages in reverse (complete first, shipping second, ...)

Good Luck,
Mark Graesser
(e-mail address removed)

----- Dwayne wrote: -----

I need to create a formula that will enter a value based
on the location of the first blank cell in a row.

I am tracking the progress of projects in my company and
have set up a spreadheet as follows. My goal is to have
the current status automatically entered as I update the
project.


STATUS PART# PROTOTYPE PRODUCTION SHIPPING COMPLETE

production 123 X n/a
complete 124 X X X X
shipping 125 X X
prototype 126 n/a n/a



In the example, I want to create a formula in the STATUS
column that will automatically input the column heading of
the current status for each part.

An "X" in the table indicates the completion of that
stage, so for PART# 123, the PROTOTYPE stage is complete
and the current stage is PRODUCTION so "production" is
entered in the STATUS column for this part.

The only exception is if the part is marked COMPLETE, then
the STATUS needs to be marked "complete".

Thanks for your help,
Dwayne.