Create Dynamic Validation List & Then Pull Related Data into Sheet

[AlyKat]

Hello Excel Gurus.

I've spent a couple days searching MANY forums for a solution to 2 tasks i'm trying to complete with No Luck
to what i'm looking to accomplish.

I have a Sample WB to reference.
SampleWB.xlsm

OK, First I'd like to see if i can Create a Drop Down list on the "Today" sheet in "A1"
that is Dynamically Populated with all the Dates found on the "Data" sheet. This data is the final, cleaned up
version of data pasted in there after a macro runs to get it looking the way is does in the sample. The amount of columns
will remain the same after every clean up but the # of rows can be variable along with the potential amount of names and which
rows the dates fall on.

Second, the end goal is to be able to select a date from the drop down list and then create a list of people working that day
and their shifts. Anyone who is Off or has a value other than a time listed would default to Off.


I always try to self serve before posting as i'm trying to learn on my own but obviously need some help with
this. I respect all your input and Thank You All in advance for any and all replies and pointers.



Thanks
Scott

[simarui]

i don't think it's possible to have a data validation list populate from nonconsecutive cells like that... my first suggestion would be to loop through your data looking for "EMP Name" and then pulling the dates from that row into another range (probably on a new sheet or something). from there it's pretty easy - could do a variable named range for your data validation list using index() or offset(), or could just include that in the code that ran through the loop in the first place...

but, since you mention a previous macro which puts it in the current format I have to wonder - can we not change that format? it generally makes everything more difficult to arrange data in a way in which headers appear on multiple rows... what if all your dates were in column A, and your employee names were in row 1? that would make it much easier to create your data validation list...

[AlyKat]

i don't think it's possible to have a data validation list populate from nonconsecutive cells like that... my first suggestion would be to loop through your data looking for "EMP Name" and then pulling the dates from that row into another range (probably on a new sheet or something). from there it's pretty easy - could do a variable named range for your data validation list using index() or offset(), or could just include that in the code that ran through the loop in the first place...

but, since you mention a previous macro which puts it in the current format I have to wonder - can we not change that format? it generally makes everything more difficult to arrange data in a way in which headers appear on multiple rows... what if all your dates were in column A, and your employee names were in row 1? that would make it much easier to create your data validation list...

Hello Simarui

Thanks so much for your reply and suggestions.

As for the validation list, i am fully open to what you suggest there but due to my minimal coding experience
i'm unsure how to build up a macro to re-sort like that. My current macro is pretty basic and just cleanup up
data that is pasted in from another source to un-merge some cells and delete blank columns and rows to get it
where it is in the example. Even if i skip the date validation list and just manually put in a date of the data i want to pull,
i'm open to that as well but like i said, my beginners coding experience is why i'm reaching out for help.

Totally "Not" expecting this to be done for me but any and all help is much appreciated as i learn,
I aspire to be as good as many of you here and do my best to self help, look up and read as much as possible
as i love all the possibilities that can be done in excel through VBA etc.

Thanks Again

Scott

[simarui]

this code transposes all your data from "Data" into a new tab called "New Data" and then pulls the dates listed into a validation list in A1 of the tab Today.

Please Login or Register  to view this content.

it should be easier from here to pull in your shift information but i hit a snag there since all your information is text - i don't know a way off the top of my head to see how many people are working on a given day using formulas... it should be doable with some more vba, or if you are ok to just include all of your employee names (returning "OFF" or w/e for those employees who are not working you could use a pretty simple hlookup(match())
like:

Please Login or Register  to view this content.

as in the attached.

i'm out for the weekend. will try to check back in on Monday - either way if you don't get a hit just reply again with something like "bump no response" or w/e to push this thread back to the top of what's new and someone else should be able to help you out.

[AlyKat]

Thanks So Much Simarui

I will check this out on my full data and see what happens.

Thanks So Much for you replies.
Much appreciated!! :-)

Scott

[AlyKat]

Hello Simarui

Thanks Again for your help thus far.
I'm running into a bit of a snag. I tried looking and tweaking based on my data
but i still seem to have a chunk or date data missing. I've re-uploaded the sheet you worked on with
actual data (Names adjusted for confidentiality) and ran with your code as is.

Copy of SampleWB.xlsm

Also a secondary question as i have yet to see this, what does the (3) in this code do?
Tried to look-up answer but didn't find any answers online.

Please Login or Register  to view this content.

Thanks Again
Scott

[AlyKat]

Hello Everyone

Bumping this thread back up as Simarui is away for the weekend and
i'm still trying to make the code work. Please see the above posts and updated sample WB
and see if you have any further suggestions.

Thanks Again To Everyone on this board as all your help
on other people's questions is really helpful.

Scott

[simarui]

Hi AlyKat,

I'm a little confused - i opened your new sample workbook and ran my code and it looks like it's working to me... i see the blank entries for the early April data in your original file but when i run the code that does not happen, and i'm really struggling to come up with a reason that it would... can't really trouble-shoot since i'm not having any issues.

in answer to your question, the 3 is the same as saying xlUp.

[simarui]

actually, just noticed the last row gets overwritten w/ blank values as the "searchdte" returns a blank several times... fixed that and did a couple other clean-up / optimization type lines in this code:

Please Login or Register  to view this content.

[AlyKat]

Hello Simarui

Thanks for the reply and hope you had a great weekend.

I'm still seeing the same problem whereas chunk of schedule are missing. (April 1st to April 12 has the dates but no schedules as well as May 1st to 3rd)

I have stepped through the entire run of the script and here's what i see happening. I'v also color coded the re-attached sample in blocks of the 7 days of schedules it
writes with every round of employees until it finds the next date batch. If you just clear the contents and run the script, you should see what i see (Hopefully).

Copy of SampleWB.xlsm

• First it writes the first 7 dates (03-22 to 03-28) and populates the 17 schedules fine.
• Next 7 dates (03-29 to 04-04) ONLY writes the schedules for 03-29 to 03-31 (3 days). Leaves 04-01 to 04-04 Blank for the 17 employees
• Next 7 days (04-05 to 04-11) - This "Seems" to overwrite the 03-29 to 04-04 range of schedules (But not the dates) as it appears to overwrite the schedules on 03-29 to 03-31 and still Leaves 04-01 to 04-04 Blank for the 17 employees.
• Next 7 days (04-12 to 04-18) Leaves 04-12 with no schedules and writes the rest for 04-13 to 04-18
• Next 7 days (04-19 to 04-25) - All Seems OK
• Next 7 days (04-26 to 05-02) Writes the first 5 days of schedules but not the last 2 days of May 1st and 2nd.
• Last Day (05-03) Stays Blank

I Hope the above helps.

Thanks again for being so helpful with this. You help is really appreciated.
Scott

[simarui]

i'm still not having that issue... makes me think there is something unique about those particular date entries on your machine - dates can be kinda fishy in excel sometimes as they are actually stored as an integer # of days past 1/1/1900 (i.e. 4/1/2015 is actually 42095, 4/2 would be 42096), but these integers are typically formatted to look like a date and excel will handle that automatically.

your original data does not hold to this convention and it gave me lots of issues writing the code in the first place (i can't even figure out how to write dates in the format provided... if i edit one of those cells and hit return excel automatically converts them to dates...) so this makes it seem like there must be a setting related to date formatting somewhere which is different on our machines... I'm really not sure what to do with that information except to suggest that you look very closely at the differences between 3/31 and 4/1 in your dataset (are they formatted differently?). also, i changed a line of that code to transfer the values instead of the formula... both work equally well for me but maybe there will be a difference for you:

Please Login or Register  to view this content.

[AlyKat]

Thanks Again for all your help thus far.

This is Very Strange. When i look at the original data (Which all comes from the same source)
i see those "problem" dates listed with that little green info box in the upper left corner of the cell.

Snapshot.JPG

The cell properties of ALL date cells are the same (General) for cells this is working properly and those that are not.

I'm kind of stumped as well but will play around and see what i can do.
This seems to bee the same issue in both Excel 2007 and Excel 2013.

Thanks Again for all your done so far.
Again very much appreciated.

Scott

[AlyKat]

Thanks Again for your help Simarui

I find if i just accept the 2 digit year correction to the 4 for those cells
the macro runs perfectly as you see on your side.

I will try to see why mine has the date issue and deal with that separately.

I will mark this thread as solved since you did an awesome job.
Again I Thank You.

Scott

[simarui]

i have that error for all dates in your original dataset... what about in the "New Data" tab? also, did you try the new code w/ the values fix..? did that do anything to help?

glad i could help. good luck w/ that data - not the most fun source i've ever seen

[AlyKat]

i have that error for all dates in your original dataset... what about in the "New Data" tab? also, did you try the new code w/ the values fix..? did that do anything to help?
glad i could help. good luck w/ that data - not the most fun source i've ever seen

Weird as i only see the error for those specific dates mention despite they ALL look the same and also have the same cell formats and properties.
In the New Data tab, and is the same with the most recent code, "ALL" dates in the Column show as 4 digit year but still those previously mention problem dates still have blank schedules
despite listing as a 4 digit year.

Thanks Again
Scott

[simarui]

when stepping through the code, do you know you can hover over variables to see what their value currently is? try stepping through it and hovering over "searchdte" to see what is pulling in there...

[AlyKat]

When I step through the code as suggested, those "problem" cells with those dates are switching the date from what is showing as 4/1/15 (April 1 2015) in Original data
and then when writing on the New Data sheet the date "writing" as 2015-04-01 (Correctly) but the "searchdte" is reading "2004-1-15" (January 15 2004).

Even when i accept the 2 digit date error to covert to 4 digit year in those cells, the date switches to 2004 as well but will write the shifts (albeit the date is way off in 2004)

A Little baffled on what to do and why this is only showing for some dates and it corrects top the wrong date when converted to a 4 digit year.

Scott

[simarui]

Hey Scott,

sorry i missed this post - i suspect it has something to do with our regional settings - for some reason your machine is defaulting to a YY/MM/DD input instead of a MM/DD/YY input. I'm not actually certain which regions follow which convention, i'm pretty sure China and probably most East Asian countries put year first, but I thought Europe / other parts did DD/MM/YY...

rather than speculate further i looked it up. Looks like it might even be a windows setting instead of just an excel setting:
http://excelsemipro.com/2011/06/regi...mats-in-excel/
https://support.office.com/en-ca/art...5-db95123d273e
https://support.office.com/en-nz/art...rs=en-NZ&ad=NZ
http://superuser.com/questions/73037...erns-in-formul


hope this helps!