shift help

[Throughstream]

Hi I have this document where column A is the persons name and the columns B-E represent what job they do

What I want to be able to do is put a persons name in I4 and another in J4 and if the can both same job (i.e CT) I would like k4 come up with "error".

shift .jpg

Note that some people can do more than one job.

Many thanks

[humdingaling]

Attach a sample workbook.
No one likes to data entry especially when trying to help someone else

Q:
so p5 and p6 wont trigger error? because one on CT and other on cardiac right?
meaning any person with 2 job types will automatically not trigger error?
is this correct?

[Throughstream]

Hopefully that uploaded

[Throughstream]

Attach a sample workbook.
No one likes to data entry especially when trying to help someone else

Q:
so p5 and p6 wont trigger error? because one on CT and other on cardiac right?
meaning any person with 2 job types will automatically not trigger error?
is this correct?

Hi yes they would trigger the 'error' as they can do either cardiac or ct so they can't be with anyone that could do cardiac or ct or both. Some people do nothing so they could do with anyone. Hope that makes sence, I've attached a sampled above. The idea behind this is for allocating night shifts, I can't have two people away from the department that can do the same job area as they need to be in during the day.

Is it possible as a formulae rather than a macro

Cheers, any help is much appriciated

Dave

[humdingaling]

using named ranges
Shift1 =OFFSET(Sheet1!$B$1,MATCH(Sheet1!$H$4,Sheet1!$A$2:$A$37,0),0,1,4)
Shift2 =OFFSET(Sheet1!$B$1,MATCH(Sheet1!$I$4,Sheet1!$A$2:$A$37,0),0,1,4)

Formula to go into J4

Please Login or Register  to view this content.

From testing seems to work

ps if you enter something into shift 1 or 2 that doesnt exist...Error = N
dont know if this is going to be an issue or not.
you can change input with DV cells if this an issue

that would be much easier than trying to fiddle with the formula

[Throughstream]

Hi that's perfect nice one

[humdingaling]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.