Some kind of crazy lookup

  • Thread starter Thread starter adam.dring
  • Start date Start date
A

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
 
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
Click to expand...
 
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
 
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 :-)
 
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
 
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
 
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
 
Back
Top