Help linking 2 workbooks extracting column header based on date.

Hi All!

This is my 1st post. Thanks in advance of any help

So a brief outline of what I'm trying to achieve - I have 2 workbooks, 1 is a holiday tracker (this is the workbook I want to import data in to), the other is a production schedule (this is the workbook I want to extract data from). the production schedule changes often, so I require any changes made in the production schedule to auto update into the Holiday Tracker.

I've attached a template of the holiday tracker - each sheet of the tracker contains 7 days, i've set macros up to create another 51 sheets (1 for each week), also to add the correct dates in for each day, and another to rename all 52 sheets with the correct week commencing date.


On the production schedule (redacted version attached), I have several columns containing lists of dates for several different processes, I need to import what process is on, for any particular date, based on the Production Schedule,
and add it to Cells C3, K3, S3 AA3, C35, K35 & S35 of the Holiday Tracker, for all 52 weeks.

So for instance - if column N on production schedule, contains a date that matches the date in the holiday tracker, it will display what processes are on for that day. also need to do this for columns - V,Z,AF,AG

FYI, I have no control over the production schedule, this is stored on a server.


I'm not a total excel noob, I've probably got just enough knowledge to get myself into trouble ha. I've watched loads of videos exploring VLOOKUP and XLOOKUP but cant seem to figure out how to work it.

hope this is enough info, if not please let me know what further info you require.

any help would be very much appreciated.


thanks

Regards

Hi JeteMc, thanks for your reply.


the dates shown are old dates, just for an example of what i'm trying to accomplish, these dates of from last season. I'm looking to update it for next season going forward. the columns and formatting of the manufacturing schedule will remain the same.
The holiday Book runs from 01 Jul - 31 Jun, as that's when our holiday entitlement begins and ends.

1. The crossed out dates are for batches planned, but they have either been cancelled or moved at a later date. these dates are ok to get included if necessary.
2. no I don't have any control of the the manufacturing schedule, that's controlled by a different department. Could I link that worksheet into the holiday book?

Hope this helps, please let me know if you require more info.

regards

bigsy73

Hi JeteMc

That's brilliant, thanks for your help.

just one quick question, I can't add the column (R) onto the process schedule (I don't have access), could I link the "process schedule" sheet in "production schedule" workbook, to my "holiday book" (on a separate worksheet), then add column (R) to my "Index" sheet in "holiday book"?

or

If I link "Process Schedule" worksheet to a new sheet in holiday book, could I add column (R) onto the process schedule sheet, without it altering the original source file?

thanks

regards

That's great, thank you so much JeteMc