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?

 

[rwe1187]

I want to thank both ExceLoki & Peter_SSs. I could not for the world get ExcelLoki's answer to work...I literally tried for hours upon hours. Then Peter_SSs sent his answer, and while I did not "USE IT TRULY" the way he displayed his answer made me aware that I named the sheets incorrectly when using the answer provided by ExcelLoki. Once I applied the sheet names, which I did not pickup on in the beginning, everything works smoothly so far. All I did was to see if I could duplicate ExcelLoki's sheets step by step. Now, I just have to apply the sheets he provided into my current sheets and all should be well. Again, thank you both very much for all your time. While I did not use explicidly use Peter_SSs's answer in this issue I was looking to resolve, I know where I can use it in another area.

 

[Peter_SSs]

Glad we could help. Thanks for the follow-up.

 

[rwe1187]

Since everyone has been helpful here, I just do not know if I am that stupid or what. I was able to do these back in the days with no problems. I have 7 tabs, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday & Spreadsheet. I basically want to check cell c4 in sheets Monday, Tuesday, Wednesday, Thursday, Friday & Saturday for "PTO" or "VACATION" and if either is in any of those cells on any of those sheets, place an error that says "Check Time Totals" in cell K1 on the sheet SPREADSHEET using conditional formatting, unless there is an easy way. Thank you ahead of time.

 

[Peter_SSs]

place an error that says "Check Time Totals" in cell K1 on the sheet SPREADSHEET using conditional formatting,

Conditional Formatting won't place an error message in K1.
Does this normal formula in K1 do what you want?

=IF(COUNT(SEARCH({"PTO","VACATION"},TEXTJOIN("|",,Monday!C4,Tuesday!C4,Wednesday!C4,Thursday!C4,Friday!C4,Saturday!C4))),"Check Time Totals","")

 

[rwe1187]

Conditional Formatting won't place an error message in K1.
Does this normal formula in K1 do what you want?

=IF(COUNT(SEARCH({"PTO","VACATION"},TEXTJOIN("|",,Monday!C4,Tuesday!C4,Wednesday!C4,Thursday!C4,Friday!C4,Saturday!C4))),"Check Time Totals","")

Yes, that works. Thank you again for your help. About a decade ago, I could do these things with no issue. I got away from it and with no practice, I seem to have lost the ability to do even the easiest Excel tasks. Thank you again.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

I got away from it and with no practice, I seem to have lost the ability to do even the easiest Excel tasks.

I know that feeling in relation to other things in my life. "Use it or lose it" I guess.

 

[rwe1187]

I know I am getting old on here... I am trying to create a formula that will auto populate a PO order#. The creation of the numbers is straight forward. It is the 6 digit date followed by HVRE. So if I placed an order today it would auto populate 060922HVRE. Tomorrow would be 061022HVRE. Today next year would be 060923HVRE. Thanks for any help.

 

[etaf]

ignore

this will change each day

which you dont want - you want anything created to remain
=TEXT(TODAY(),"MMDDYY")&"HVRE"

Book6
	A	B
1
2	090622HVRE
3
Sheet1

Cell Formulas
Range	Formula
A2	A2	=TEXT(TODAY(),"DDMMYY")&"HVRE"

strange XL2BB has decided to change the format

 

[Peter_SSs]

I am trying to create a formula that will auto populate a PO order#.

If you want the PO order number created today to not update to tomorrow when you open the sheet again then, you will need to use vba rather than a formula. Is that acceptable?
Do you ever create more than one PO order in the same day? If so, do they both get the same PO order number? If not, how should they differ?
When a new PO order number is generated, where does it go?

strange XL2BB has decided to change the format

Can you explain what format you had and how that differed to the format appearing in your post? Perhaps images to show?

 

[etaf]

Can you explain what format you had and how that differed to the format appearing in your post? Perhaps images to show?

i have started a conversation, so I dont take this thread off subject , hope thats OK