Dont want same person to be booked for same shift twice

Hi all,

I am designing a excel sheet which is meant to help us manage day and night shifts for several people at different sites. I have a drop down menu in cells in the left most column to select from a list of names and then 7 column on the right for each days with day/night dropdown option with color formatting for days and nights. My problem is, I don't want it to be possible for someone to be booked for same shift in two different rows and I don't know how to achieve that. for example if mr A is booked for Monday day shift, it shouldn't be possible to book him again in a different row for the same shift.

can anyone help me with this? I have attached a screenshot of the file here.

Thanks
list.jpg

you maybe able to add a countifs to the data validation
so something like
countifs( column A, CellA, column I , cellI)>1

if you could attach the sheet ,we should be able to work an example and post - rather than look at images and retype all the info into a spreadsheet for an example

also add some names - I was not expecting to see MR-A appear in more than 1 row

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

hi triveni6, welcome to the forum. here are the steps i did:
1. use this array formula in F2 onwards:
=IFERROR(INDEX(B:B,SMALL(IF(ISNA(MATCH($B$2:$B$6,Sheet1!$A$3:$A$32,0)),ROW($B$2:$B$6)),ROWS(F$2:F2))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

2. press CTRL + F3 to create a Named Range. i gave it a name "NameRange". insert this formula:
=$F$2:INDEX($F:$F,COUNTIF($F:$F,">""")+1)

3. go to Sheet1 & select A3:A32 -> Data Tab -> Data Validation -> Allow -> List -> Source:
=NameRange

done

I'm not sure how to get the dropdown list of night and day along with a custom validation using

=OR((COUNTIFS(A:A,A3,I:I,"Night")>1),(COUNTIFS(A:A,A3,I:I,"Day")>1))

that will flag up if anyone is selected for two or more "night" or two or more "days" on the same Day
so to copy along the other columns

=OR((COUNTIFS($A:$A,$A3,I:I,"Night")>1),(COUNTIFS($A:$A,$A3,I:I,"Day")>1))

I think you may need a VBA solution

tried a few things to add a list - but data validation does not accept the array

hmmm try this then. select from I3:O32 & use this formula in the Data Validation List:

Formula:

Please Login or Register  to view this content.

note that your "Day " in Sheet2!E2 contains a space behind. delete that space. untested in Excel 2007. should work still. if you need anything more than this, then you'll probably need VBA.