Some kind of crazy lookup

[adam.dring]

My first post, so be gentle.....

I have a worksheet with a list of name in column A, with row A1 Heade
"Name". B1 to J1 contain dates (the first of each month June 04
February 05) and for each name columns B thru J contain the value
"assigned" or "unassigned".

If that didn't make any sense, it should look something like this

Name 01-June-04 01-July 04 01-August-04 Etc

Ade Ogu Assigned Assinged
Unassigned Etc
Adrian Davis Assigned Assigned
Assigned Etc
Adrian Sheehan Unassigned Unassigned Unassigned
Etc
Adrian Spary Assigned Unassigned Unassigned
Etc
Aileen Jolly Unassigned Assigned
Unassigned Etc

now what I want to happen in column K is, for each name going down th
list, the first "Unassigned" date is listed, if there no unassigned'
then I would like 01-March-05 to be listed, it may be worth mentionin
that after someone has become unassigned, they can become assigne
again, and even unassigned after that (i.e. aileen jolly above) i
which case i am only interested in the first time that it happens.

For some of you guru's on here this is probably very easy stuff, al
help is appreciated.

thx

Ada

 

[Frank abel]

Hi
try the following array formula (entered with
CTRL+SHIFT+ENTER) in K2:
=IF(MIN(IF(B2:J2="unassigned",COLUMN(B2:J2))),INDEX
($B$1:$J$1,MIN(IF(B2:J2="unassigned",COLUMN(B2:J2)))-
1),DATE(2005,3,1))

-----Original Message-----
My first post, so be gentle.....

I have a worksheet with a list of name in column A, with row A1 Headed
"Name". B1 to J1 contain dates (the first of each month June 04 -
February 05) and for each name columns B thru J contain the values
"assigned" or "unassigned".

If that didn't make any sense, it should look something like this

Name 01-June-04 01-July 04 01- August-04 Etc

Ade Ogu Assigned Assinged
Unassigned Etc
Adrian Davis Assigned Assigned

 

[JulieD]

Hi Adam

=OFFSET($A$1,0,MATCH("unassigned",B1:J1,0))

you might need to enter it using ctrl, shift & enter although it seemed to
work for me without doing this - you'll also need to format the results
using a date format.

Cheers
JulieD

 

[Frank Kabel]

Hi Julie
but this will work only if there's at least one occurence
of 'unassigned'. If this is not the case this would return
an error and not 03-01-2005 as required by the OP

 

[JulieD]

Hi Frank

(good to see you back - hope all went well)

oops forgot that bit ...what about

=IF(ISNA(OFFSET($A$13,0,MATCH("unassigned",B17:J17,0))),38355,OFFSET($A$13,0
,MATCH("unassigned",B17:J17,0)))

Cheers
JulieD

 

[Max]

Perhaps this might help ..

Put the date "01-March-05" in K1

Put in K2:

=IF(ISNA(MATCH("Unassigned",B2:J2,0)),K$1,OFFSET($A$1,,MATCH("Unassigned",B2
:J2,0)))

Copy K2 down as many rows as there are names in col A

 

[Frank Kabel]

Hi Julie

Hi Frank

(good to see you back - hope all went well)

thanks for asking. Still quite busy but not as stressful
as the last weeks

Cheers
Frank

 

[Max]

Missed out this earlier ..:

Format K2 as date before copying down

 

[adam.dring]

Thank you, your good people, this was a big help, sorry for the slo
feedback, but i'm doing 3 people's jobs right now.

Ada