Excel Data Validation

[rwe1187]

So I am not sure if this is possible. I want to use Data Validation to work the following:

Let's say I have a sheet with a list of lockers. This is the LOCKERS sheet. All the locker numbers are listed in column A. In column B there is Assigned/Status. This column has one of the following for each locker number: A name that the locker is assigned to...or the word BROKEN which means they are unavailable....or the word OPEN which means they are available. Column C is the shift number which is not really important for this. On the sheet I want to use Data Validation, which we will call employee tracking, I would like a drop down list displaying only the locker numbers that are available. On this sheet is also a list of employees. So as I assign the lockers on the EMPLOYEE TRACKING sheet the LOCKERS sheet will have to update somehow so that the proper lockers are shown in the data validation drop down menu.

Sound like something that can work?

 

[ExceLoki]

is this what you mean?
----------------------

Excel Data Validation Lockers.xlsx
	A	B	C
1	Locker Number	Assigned/Status	shift number
2	1	Liam
3	2	Noah
4	3	Oliver
5	4	Theodore
6	5	Olivia
7	6	Broken
8	7	Emma
9	8	Open
10	9	Open
11	10	Open
12	11	Open
13	12	Open
14	13	Open
15	14	Charlotte
16	15	Broken
17	16	Sophia
18	17	Open
19	18	Evelyn
20	19	Open
21	20	Open
LOCKERS

Cell Formulas
Range	Formula
B2	B2	=IF(MATCH(LOCKERS!A2,'EMPLOYEE TRACKING'!B:B,0)=TRUE,"Open",XLOOKUP(LOCKERS!A2,'EMPLOYEE TRACKING'!B:B,'EMPLOYEE TRACKING'!A:A))
B3:B6,B17:B21,B8:B15	B3	=IFERROR(IF(MATCH(LOCKERS!A3,'EMPLOYEE TRACKING'!B:B,0)=TRUE,"Open",XLOOKUP(LOCKERS!A3,'EMPLOYEE TRACKING'!B:B,'EMPLOYEE TRACKING'!A:A)),"Open")

----------------------

Excel Data Validation Lockers.xlsx
	A	B	C	D
1	Name	Locker Number		Open Locker Numbers
2	Liam	1		8
3	Noah	2		9
4	Oliver	3		10
5	Elijah			11
6	James			12
7	William			13
8	Benjamin			17
9	Lucas			19
10	Henry
11	Theodore	4
12	Olivia	5
13	Emma	7
14	Charlotte	14
15	Amelia
16	Ava
17	Sophia	16
18	Isabella
19	Mia
20	Evelyn	18
21	Harper
EMPLOYEE TRACKING

Cell Formulas
Range	Formula
D2:D21	D2	=IFERROR(INDEX(LOCKERS!A2:A21,SMALL(IF("Open"=LOCKERS!B2:B21,ROW(LOCKERS!B2:B21)-ROW(LOCKERS!B2)+1),ROW(LOCKERS!1:1))),"")

Cells with Data Validation
Cell	Allow	Criteria
B3:B21	List	=$D$2:$D$21

 

[rwe1187]

Thanks for the input. But, I am not sure this is what I am looking for, and I can already tell that I will get frustrated trying to explain it. Let me try one more time, and then I will call it quits.

Two sheets: Lockers & Employee Tracking

LOCKERS sheet has column A with cells that have locker numbers that remain constant.
LOCKERS sheet has column B with cells that have Assignment or status that can change. There is a drop down menu where you can choose either AVAILABLE or BROKEN or you can simply have an employee's name that the locker number was assigned to. This can change as employees come and go and as lockers change status - AVAILABLE ones become BROKEN and BROKEN ones are fixed and become AVAILABLE.

EMPLOYEE TRACKING sheet has column A that has the employee's name that will be entered.
EMPLOYEE TRACKING sheet has column C that has employee's end date that will be entered.
EMPLOYEE TRACKING sheet has column M that has LOCKER NUMBER. In column M, I would like a drop down list where only a list of lockers that are AVAILABLE from the LOCKERS sheet, appears. As the lockers on the LOCKERS sheet change from AVAILABLE to BROKEN or are assigned they would not display in the cells in column M. As they change from BROKEN to AVAILABLE or when an employee leaves and they become AVAILABLE they would appear in the drop down list in column M.

I would like column B on the LOCKERS sheet to update automatically with the name of the employee from the EMPLOYEE TRACKING sheet as the lockers are assigned. Also have the ability to enter the status in column B on the lockers sheet as BROKEN as lockers become broken or AVAILABLE will show if a date is entered in column C on the EMPLOYEE TRACKING sheet.

This probably sounds so confusing. It is easier to show someone vs typing an explanation. I understand if it cannot be done. I actually am sure it can be accomplished, just maybe not with my explanation LOL.

So an example would be:

On the Lockers page there are lockers 1-5. Column B shows1-4 as available and 5 as broken (manually keyed in as broken).
On the Employee Tracking sheet, I enter Rosa G as the employee....work my way to column M and a drop down menu appears showing lockers (Either can be 1-4 since they are the only ones available or 1-5 maybe in black and 5 in red since it is broken and cannot be chosen). I choose locker 1 for Rosa. If I go back to the lockers sheet, locker 1 now shows as Rosa G (not available or broken). Back to the employee tracking sheet. Employee number 2 is John R. I move to column M and now lockers 2-4 appear (locker 1 belongs to Rosa and does not show and locker 5 is broken). John R gets locker 2. Back on the lockers page locker 2 now shows as John R. later on Rosa has an end date entered in column C on employee tracker. Back on the locker sheet locker 1 now shows as Available. I go to enter Larry Smith as a new employee and in column M locker 1 is now available again. And this keeps happening.

I hope this was enough, yet not too much.

 

[ExceLoki]

ok... so i think i've got a solution for you if you can make a few edits to your sheet...
-will need a helper list somewhere for the lockers numbers available- i added a "hidden" sheet to have this on
-add a separate status column on the lockers page
----------------

Excel Data Validation Lockers.xlsx
	A	B	C
1	Locker Number	Assigned	Status
2	1	Liam	Available
3	2	Available	Available
4	3	Elijah	Available
5	4	Available	Available
6	5	Broken	Broken
7	6	Available	Available
8	7	Noah	Available
9	8	Available	Available
10	9	Available	Available
11	10	Available	Available
12	11	Available	Available
13	12	Available	Available
14	13	Available	Available
15	14	Available	Available
16	15	Available	Available
17	16	Available	Available
18	17	Olivia	Available
19	18	Available	Available
20	19	Available	Available
21	20	Available	Available
LOCKERS

Cell Formulas
Range	Formula
B2:B21	B2	=IFERROR(IF(MATCH(LOCKERS!A2,'EMPLOYEE TRACKING'!M:M,0)=TRUE,"Open",IF(XLOOKUP(LOCKERS!A2,'EMPLOYEE TRACKING'!M:M,'EMPLOYEE TRACKING'!C:C)="",XLOOKUP(LOCKERS!A2,'EMPLOYEE TRACKING'!M:M,'EMPLOYEE TRACKING'!A:A),C2)),C2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:C21	Cell Value	="Broken"	text	NO
B2:C21	Cell Value	="Available"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C2:C21	List	Available, Broken

----------------

Excel Data Validation Lockers.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name		End Date										Locker Number
2	Liam												1
3	Noah												7
4	Oliver
5	Elijah												3
6	James		1/1/2022										3
7	William
8	Benjamin
9	Lucas
10	Henry
11	Theodore
12	Olivia												17
13	Emma
14	Charlotte
15	Amelia
16	Ava
17	Sophia
18	Isabella
19	Mia
20	Evelyn
21	Harper
EMPLOYEE TRACKING

Cells with Data Validation
Cell	Allow	Criteria
M2	List	=IF($C$6="",'Available Lockers Hidden'!$A$2:$A$17,'Available Lockers Hidden'!$B$2)
M3:M21	List	='Available Lockers Hidden'!$A$2:$A$17

----------------

Excel Data Validation Lockers.xlsx
	A
1	Available Lockers
2	2
3	4
4	6
5	8
6	9
7	10
8	11
9	12
10	13
11	14
12	15
13	16
14	18
15	19
16	20
17
18
19
20
21
22
Available Lockers Hidden

Cell Formulas
Range	Formula
A2:A22	A2	=IFERROR(INDEX(LOCKERS!$A$2:$A$21,SMALL(IF("Available"=LOCKERS!B2:B21,ROW(LOCKERS!B2:B21)-ROW(LOCKERS!B2)+1),ROW(LOCKERS!1:21))),"")
Dynamic array formulas.

 

[rwe1187]

I am not sure I follow this at all. I tried and am coming up with all kinds of weird items and responses. When I copy and try to paste the first formula you have it opens up a window and is looking for a file name. At the top it says UPDATE VALUES:LOCKERS and is looking for a file name. I have no clue what to "point" it at.

 

[rwe1187]

And on the lockers sheet it is copying over from column c to column b but even with the conditional formatting in it will not change the color in column b, but it does for column c

 

[rwe1187]

Never mind the last comment. The color works. I just dont know where to point to when I try and paste the formula in column B

 

[rwe1187]

So I duplicated what you did step by step as far as I can see and I cannot get anything to work properly. I am using Office 365 if that makes a difference. Every column has a formula in it, so I do not even know where to update the Locker numbers and names. Along with that, everytime I open up wither workbook, I get two errors. One is a security error that I have resolved. The next one is a reference error. I get a workbook contains links to one or more blah blah blah that could be unsafe....UPDATE...... DON'T UPDATE........CANCEL...... I guess I would choose UPDATE and when I do I get "We cant update some of the links in your workbook right now. You can continue without updating their values, or edit the links you think are wrong". CONTINUE.....EDIT LINKS...... Continue does nothing. Edit links opens up another box called an edit links box. It has a source, type, update and status column. The first row shows Source = Employee Tracking, TYPE = Worksheet, UPDATE = A, and STATUS = Error: Source not found. The next line is all the same except the SOURCE = Lockers. The choices I have are UPDATE VALUES, CHANGE SOURCE, OPEN SOURCE, BREAK LINK, CHECK STATUS or CLOSE. CLOSE does nothing and the others want me to change items.

 

[rwe1187]

I think the problem is that everytime I go to paste a formula it opens an UPDATE VALUES prompt and I do not know what to choose.

 

[Peter_SSs]

My suggestion is somewhat similar but see it could work for you.
Dedicate a column in the LOCKERS sheet to mark the broken lockers. I have used column D.
Enter the formulas shown in B2 and E2, no need to copy them down. (They will 'spill' down) You will need to check the ranges though.

rwe1187.xlsm
	A	B	C	D	E
1	Locker Number	Assigned/Status	Shift Num	Broken	Open
2	1	Employee 3			2
3	2	Open			6
4	3	Broken		Broken	12
5	4	Broken		Broken	15
6	5	Employee 1			17
7	6	Open
8	7	Employee 4
9	8	Broken		Broken
10	9	Broken		Broken
11	10	Employee 6
12	11	Employee 9
13	12	Open
14	13	Broken		Broken
15	14	Employee 7
16	15	Open
17	16	Broken		Broken
18	17	Open
19	18	Broken		Broken
20	19	Broken		Broken
21	20	Broken		Broken
LOCKERS

Cell Formulas
Range	Formula
B2:B21	B2	=IF(D2:D21="",XLOOKUP(A2:A21,'EMPLOYEE TRACKING'!B$2:B$10,'EMPLOYEE TRACKING'!A$2:A$10,"Open"),D2:D21)
E2:E6	E2	=FILTER(A2:A21,B2:B21="Open","")
Dynamic array formulas.

Set up the EMPLOYEE TRACKING Data Validation like this.

rwe1187.xlsm
	A	B
1	Employee	Locker
2	Employee 1	5
3	Employee 2
4	Employee 3	1
5	Employee 4	7
6	Employee 5
7	Employee 6	10
8	Employee 7	14
9	Employee 8
10	Employee 9	11
EMPLOYEE TRACKING

Cells with Data Validation
Cell	Allow	Criteria
B2:B10	List	=LOCKERS!$E$2#

One thing to note with this is that you must use the drop-down to choose a locker for an employee, you cannot type it in.