Data Validation List with an "IF" statement?

[Mccann]

Hey Guys!

I own a valet parking company

I have a list for the guys that says which day of the week they are available to work.
I have a list for the guys that says which accounts they have been trained at.
I would like to have a data validation drop down list that cross references the 2 lists and shows who is trained, and who is available.

With that said, I would also like to be able to reference a cell in the same row that has a date in it.

So essentially I need to figure out how to combine the (2) lists with one of the date variable dependent on a cell value

Thanks in Advance!

 

[oldbrewer]

	skill1	skill2	skill3	combined				sun	mon	tue	wed	thu	fri	sat
dave		1	1	023			dave	1	1	1			1	1
fred	1		1	103			fred	1	1	1	1	1	1	1
bill	1	1	1	123			bill		1	1	1	1	1
sue		1		010			sue	1						1
ann	1	1		110			ann					1	1	1
								sun	mon	tue	wed	thu	fri	sat
							dave	023	023	023			023	023
							fred	103	103	103	103	103	103	103
							bill		123	123	123	123	123
							sue	010						010
							ann					110	110	110

<colgroup><col width="64" span="16" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Mccann]

Yes!

That is the logic I am looking for, now how do I get that to generate pull down lists?

 

[oldbrewer]

if you have a live table for each day showing who is working and their skill sets what more do you need

 

[Mccann]

I need it to populate the attendants name inside the schedule "list" which is being called on by the data validation option.

For example, we have 20 locations, so based on attendants availability and "skill" i would like to make it so when my office assistant pulls down the menu, it shows who can work there based on availability.

Each location has a table similar to this, I want to make it so when my assistant pulls down that list for "employee" it populates which attendant is trained and references A1 for the day of the week in which we are trying to staff. Just programing it to call on Monday/Tues etc for each line does not work well because sometimes the accounts will add (2) attendants same day, and due to various other circumstances the order of rows may change, inevitably that A1 column needs to stay dynamic.

(Im trying to replace the need for a secretary via a semi intricate automated excel workbook)

Date	Shift Start	Departure	Time Left	Employee			Job	Distance	Confirmed
Monday, July 28, 2014	5:00 PM		10:00 PM	Ortiz, Steven
Tuesday, July 29, 2014	5:30 PM
Tuesday, July 29, 2014	5:00 PM		10:00 PM	Bhyan, Razzy
Wednesday, July 30, 2014	5:00 PM		10:00 PM	Bhyan, Razzy
Thursday, July 31, 2014	5:00 PM		10:00 PM	Ortiz, Steven
Friday, August 01, 2014	4:00 PM		10:30 PM	Donnellan, Steven
Friday, August 01, 2014	4:00 PM		12:00 AM	Exum, Kenneth
Saturday, August 02, 2014	4:00 PM		12:00 AM	Bhyan, Razzy
Saturday, August 02, 2014	5:00 PM		9:00 PM	Reyes, Ever
Sunday, August 03, 2014	4:00 PM		10:00 PM	Bhyan, Razzy
Sunday, August 03, 2014

<colgroup><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

 

[oldbrewer]

do attendants work at more than one location ?

 

[Mccann]

Yes, that is the purpose of this.

Since they all have different days they can work, and some can work at some places where others can not..

I would like the logic to be "If on 'Monday Day' (availability) range & on 'Location1' range, populate in a specific drop down list.

 

[oldbrewer]

				john
			location1	location2	location3	location4	location5	location6	location7	location8
		mon	1	1		1		1	1
		tue	1	1		1		1	1
		wed	1	1		1		1	1
		thu	1	1		1		1	1
		fri	1	1		1		1	1
		sat	1	1		1
		sun	1	1
				fred
			location1	location2	location3	location4	location5	location6	location7	location8
		mon		1	1	1		1		1
		tue		1	1	1		1		1
		wed		1	1	1		1		1
		thu		1		1		1		1
		fri		1		1		1		1
		sat		1		1		1		1
		sun		1		1		1		1
				bill
			location1	location2	location3	location4	location5	location6	location7	location8
		mon	1		1	1	1	1	1	1
		tue	1		1	1	1	1	1	1
		wed	1		1	1	1	1	1	1
		thu	1		1	1	1	1	1	1
		fri	1		1	1	1	1	1	1
		sat	1		1	1	1	1	1	1
		sun	1		1	1	1	1	1	1
			location1	location2	location3	location4	location5	location6	location7	location8
		mon	2	2	2	3	1	3	2	2
		tue	2	2	2	3	1	3	2	2
		wed	2	2	2	3	1	3	2	2
		thu	2	2	1	3	1	3	2	2
		fri	2	2	1	3	1	3	2	2
		sat	2	2	1	3	1	2	1	2
		sun	2	2	1	2	1	2	1	2
											this tells you what work availability you have and the different staff tables could be linked to menu cells on the first page

<colgroup><col><col><col><col span="9"></colgroup><tbody>
</tbody>

 

[Mccann]

Yes, I used this solution to create the list names, then used concatenation to pair values determined by if( statements to generate the list name that the drop down box would call for.

Thanks all for your continued help!

 

[oldbrewer]

on your first page you could have 3 cells with john, bill and fred in them, and clicking any one of them would automatically take you to that person's availability table - if you also want to show that for example john is committed on a particular day then that is far more complicated, you manage the work at the moment, so is the improvement offered acceptable ?