Hi again, I just had a problem solved, but another arose, I need to be able to combine a list with a formula as data validation in cells. If vba works better for this, I can use that instead. Attached is an example and cells in yellow would be the ones requiring both, the list is called "Time". Thanks!
The list, Time, is the list of times. That much is clear. What is the formula? How should it be "combined" with the list? What do you hope to see in the yellow cells?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: Lá fhéile Pádraig sona dhaoibh
The formula is in the yellow cells in the data validation already, but here it is if this helps, in cell A3 is =(A3>=B2)+(B2-A3>20/24). I want them to be able to select a time from the list, but it cannot overlap previous time.
See it now. So in A4... 18:31 is acceptable, but 18:30 is not? Or am I misunderstanding you...
=(A4>B3)+(B3-A4>20/24)
Finally, I think I follow you... Now for a look.
"So in A4... 18:31 is acceptable, but 18:30 is not? Or am I misunderstanding you...
=(A4>B3)+(B3-A4>20/24)"
18:30 is acceptable, thus the = sign.
Last edited by thecdnmole; 01-03-2021 at 01:23 PM.
Now now, sarcasm doesn't suit you. Don't forget you know EXACTLY what you want. I don't.
So, I suspect this is partly what you want... and that I'll need to modify column B's DV as well. So for now, don't worry about Col B. Is col A doing what it is meant to??
YES, awesome, perfect!Originally Posted by Glenn Kennedy
Ya, sorry, you think you describe something well, but for someone else it may not be clear at all.
Last edited by thecdnmole; 01-03-2021 at 02:03 PM.
So, I have now fiddled with column B. I think that this is it.
What YOU need to do is make sure that there are no errors caused by TIME rounding times. Just make sure that
1. the next available slot in column A is >= the previous entry in column B
2. The first available slot is column B is only 1 higher than the corresponding value in column A (same row).
Thanks Glenn, your attached file works great! But, now I need to adapt it a bit for my situation as my time lists are located on a different worksheet! I know, should have mentioned that earlier, but usually those kind of things I can figure out, just need the basics. But, whatever I have tried so far isn't working, but I will keep trying. Got it working! You DID solve the problem as presented, so thanks for that, and this is actually EXCATLY how I wanted this to work and I can use this elsewhere for times!
Last edited by thecdnmole; 01-03-2021 at 02:52 PM.
OK.
Firstly, thanks for the rep.
Secondly, I was very naughty and didn't tell you what i'd done, 'cos I really wasn't sure it was what you wanted and I didn't want to waste time explaining the wrong solution. The formulae are driven by two named ranges DV_A and DV_B. (CTRL-F3 to view/edit). here's one of them:
=INDEX(Sheet1!$H:$H,IFERROR(MATCH(Sheet1!$B1,Time,0),1)+1):INDEX(Sheet1!$H:$H,MATCH(1E+100,Sheet1!$H:$H))
the other is pretty much the same. change the bits in red to suit your own set up. Actually, you can replace "Time" NR Time with Sheet1!$H:$H and it'll be fine.
If you hit a snag, just add a new post to this thread. I will check back in a.m. Beer time now.
One last thing. NRs have a tendency to "wander". Just double check that the references to A2/B1 have not changed to row1048576 when first set up. If they have, change 'em back and save (and check again).
Thanks again Glenn, MUCH appreciated! Enjoy the beer!
Hi Glenn, just curious, in the formula near the end is "1E+100", what does that do? Thanks!
It's a very large number (1 with 100 zeros after it). It's a way of getting Excel to return the last numerical value in a row. So, it defines the end point of your named range. You can add on additional times and the formula automatically adds them onto the NR.
Hi Glenn or anyone whom can answer this, is there a way to allow the list to always have 24 hours of time available to select from? The way it currently works it runs out at 0:30. Ideally, if you select the time allowed in the list should be 24 hours from the end time in cell above. Is this possible? Not sure if this is allowed or if I need to start a new thread. Sorry.
Last edited by thecdnmole; 01-09-2021 at 06:37 PM.
OK. It's the same problem. I need just a bit more information from you... From what time do you want the DD list to start? Currently it's 16:00-00:30. 16:00, something else?
We are changing to new shifts and currently running three, but if you get one figured out, I can figure out the other two, so one shift is 6:00-14:30, 2nd shift is 14:30-23:00 and 3rd shift is 20:00-6:30, but you can do the first 6:00-14:30, thanks.
HELP!!
The text from your last post:
We are changing to new shifts and currently running three, but if you get one figured out, I can figure out the other two, so one shift is 6:00-14:30, 2nd shift is 14:30-23:00 and 3rd shift is 20:00-6:30, but you can do the first 6:00-14:30, thanks.
and from Post#15:
Is there a way to allow the list to always have 24 hours of time available to select from? The way it currently works it runs out at 0:30. Ideally, if you select the time allowed in the list should be 24 hours from the end time in cell above.
seem (to me) to be contradictory. Now I am totally confused. can you have another bash at explaining... (Take a deep breath, first!!)
Ya, sorry, two different days and things change. So, let's do 10 hours. I kinda did a workaround by just extending the time out for 48 hours by using a formula, which worked, not ideal, but worked. BUT, formulas I was using elsewhere quit working properly and for the life of me couldn't figure it out. So I copied my problem exactly to a blank spreadsheet to present on here but it worked fine. Took a while but I figured it out, any duplicate times, the serial number would change which is what Excel uses in the formula, so for example, the first instance of 3:00 is 0.125, but the second instance of 3:00 returns 1.125, which is a problem in formulas! So what I figured out was to break the formula at 0:00 and start over, then Excel won't add the 1. This works, but you end up with such a long list to scroll thru, so a much shorter, eight hour list that moves, so no matter what time you start with, you always have an hour or two past the end of shift, so for a eight hour shift, one would need to have ten hours in the list.
OK. You have no idea how lucky you are to be across the other side of the Big Pond... I thought that I'd try to get smart with this... and everything turned to ashes. If you had been here beside me... I'd cheerfully have slit your throat!!
Anyhow... far too long later... it's working as specified, I hope. A real lesson in my fallibility!!
Select from the DD in B1, enter the shift length in hrs in B2 and the desired time interval in B3.
Thereafter the DDs in the blue cells work as desired and the totals work, too!!!
Sorry Glenn to cause you SO much grief and thank-you very much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks