Create Dynamic Validation List & Then Pull Related Data into Sheet

Originally Posted by 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...

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

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

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

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

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

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

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

Originally Posted by 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

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

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