• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help needed with creating values based on date then creating validation lists that will display in cell over multiple lines.

T

TerryE

New Member
Hi everyone,
apologies for the long winded vague title but please bear with me!

I have a yearly course scheduler planner that has multiple courses that run from one day to a maximum of 5 weeks. Each course has a unique code with consecutive cohort numbers for each group. It has over 75 courses and over 20 trainers. The below is just a test sample for this thread


Course Course CodeStartFinish
Introduction to MS Word C01Intro Word C01
Advanced Excel C05Adv Excel C05
Cyber Security Essentials C05Cyb Sec Ess C05
Data Security Awareness CO4Data Sec C04
[td]
06/10/2025​
[/td][td]
08/10/2025​
[/td]​
[td]
08/10/2025​
[/td][td]
08/10/2025​
[/td]​
[td]
08/10/2025​
[/td][td]
15/10/2025​
[/td]​
[td]
14/10/2025​
[/td][td]
16/10/2025​
[/td]​

This would, in another table if needed, look like this and would need to ignore dates such as Bank Holidays, Closures etc which I have stored in another table on another tab.
Any course that runs for more than one week needs to have the Week number added as well.


MonTueWedThuFriMonTueWedThuFri
Course06-Oct07-Oct08-Oct09-Oct10-Oct13-Oct14-Oct15-Oct16-Oct17-Oct
Intro Word C01Day 1Day 2Day 3 Closed
Adv Excel C05 Day 1 Closed
Cyb Sec Ess C05 Week 1 Day 1Week 1 Day 2Week 1 Day 3ClosedWeek 2 Day 4Week 2 Day 5
Data Sec C04 ClosedDay 1Day 2Day 3


From here, what I am after, for each trainer and for each date, to have a validation drop down with relevant values based on what courses and what day


MonTueWedThuFri
06-Oct07-Oct08-Oct09-Oct10-Oct
Tom
Dick
Harry

So for Monday 6th October, the drop down would only show

Intro Word C01 Day 1
and when selected, would look like this in the cell
Intro Word C01
Day 1

For Wednesday 8th October the drop down would show the following as options


Intro Word C01 Day 1
Adv Excel C05 Day 1
Cyb Sec Ess C05 Week 1 Day 1
and as before the selection would be over 2 lines.
Cyb Sec Ess C05
Week 1 Day 3

Happy to use helper columns, VBA etc.

I do currently have something a lot basic that this that filters list of courses for that particular week and then all weeks and days listed, similar to below and I have VBA that allows multiple values to be added over multiple lines but think there is a better way of doing it.
Intro Word C01
Adv Excel C05
Cyb Sec Ess C05
Week 1
Week 2
Week 3
Week 4
Week 5
Day 1
Day 2
Day 3
Day 4
Day 5
Day 6
Day 7
Day 8
Day 9
Day 10
Day 11
Day 12
Day 13
Day 14
Day 15
Day 16
Day 17
Day 18
Day 19
Day 20
Day 21
Day 22
Day 23
Day 24


Thanks in advance for any input and advice you can give.
Regards
Terry
 
Have a look at the light green shaded area (d15:h17) in the attached.
If this is what you want then we can tidy it up (it's very rough and ready at the moment).
1759746512224.png

It uses a combination of Power Query, named ranges and a one-line macro to do the job. Don't alter the formula in cell N2 (yet).
You will get some peculiar results if a course crosses over more than one year!
 

Attachments

Last edited:
p45cal said:
Have a look at the light green shaded area (d15:h17) in the attached.
If this is what you want then we can tidy it up (it's very rough and ready at the moment).
View attachment 90888

It uses a combination of Power Query, named ranges and a one-line macro to do the job. Don't alter the formula in cell N2 (yet).
You will get some peculiar results if a course crosses over more than one year!
Click to expand...
This is exactly what I was looking for. Thank you so much for this.
However, there is one additional thing I forgot to mention. In the drop down, as well as the course names, I would need a list of non training activities that are standard for all days/trainers such as appraisals, secondment, L&D Prep, Annual Leave etc? In my current version, I have these listed on a separate tab and is filtered into the relevant drop down along with the courses for that week.
Are you able to incorporate that as well?
Many thanks for your time and effort in this, I really do appreciate it
regards
Terry
 
p45cal said:
See attached.
Click to expand...
Thank you p45cal, this is excellent. I know you said it needs tidying up so perhaps I am jumping the gun here but, just to check, if I need to add new courses, activities, I would have to either manually refresh the tables or add VBA to do so?
One last question, is it possible to have a space between the values in the merged table/validation list? i.e.
Cyb Sec Ess C05Week 2 Day 4 becomes Cyb Sec Ess C05 Week 2 Day 4?
If not, I am more than happy with what you have produced, so grateful
 
TerryE said:
if I need to add new courses, activities, I would have to either manually refresh the tables or add VBA to do so?
Click to expand...
Yes, you'd need to refresh the table at cell K2 (right-click and choose Refresh) if you change the Courses table at cell C2. We could automate this if you want.
At the moment, a change in size of that table will probably shift cells around elsewhere on that sheet so perhaps put that table in a separate sheet.
A change in any other table (Hols or NonTraining) won't need a refresh.
 
p45cal said:
Yes, you'd need to refresh the table at cell K2 (right-click and choose Refresh) if you change the Courses table at cell C2. We could automate this if you want.
At the moment, a change in size of that table will probably shift cells around elsewhere on that sheet so
A change in any other table (Hols or NonTraining) won't need a refresh.
Click to expand...
"We could automate this if you want" , that would be useful thank you
"perhaps put that table in a separate sheet" good idea, I do currently have courses and schedules on separate tabs so makes sense
Thanks
 
Correction: You would need a refresh if you change the Hols table too, however, in the attached I've moved all the tables to a Tables sheet. When you move away from that sheet the query is refreshed (a one line macro).
 

Attachments

Last edited:
You must log in or register to reply here.
Back
Top