Help linking 2 workbooks extracting column header based on date.

[bigsy73]

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

[JeteMc]

Hello bigsy73 and Welcome to Excel Forum.
I see an obstacle to helping which is that the start date of the Holiday Book is Aug 16 but the latest date in columns N,V,Z,AF,AG of the Manufacturing schedule is July 16 in cell AF299 and that date is crossed through.
Questions:
1. Should the dates that are crossed through be excluded? The cross through is just visual formatting which will not affect the way Excel will interpret the data.
2. Is it possible to combine the Holiday Book and Manufacturing schedule into one workbook? I feel this will be a challenging task even for a single workbook.
3. If the answer to question #2 is yes, then could the data on the Manufacturing schedule MVB sheet be converted into an Excel table so that it could be loaded into Power Query (Get & Transform)?

[bigsy73]

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

[JeteMc]

I suggest adding a column (R) to the Process Schedule sheet and populating it using the following formula **: =TEXTJOIN(", ",1,IF(D1869:I1869="",D$4:I$4,""))
Note that in earlier versions of Excel this would need to be array entered (Ctrl + Shift + Enter) but I believe that the 365 version will just require pressing the Enter key.
The formula for the MVB PROCESSES is: =INDEX($R$1869:$R$1890,MATCH(T1869,$C$1869:$C$1890,0))
Note that I have put the "Template" on the Process Schedule sheet. I believe that when the "Template" is cut and pasted into the Holiday Book the formula will update to include a link to the Manufacturing schedule workbook.

[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

[JeteMc]

Here is what the TEXTJOIN formula looks like in another workbook:

Please Login or Register  to view this content.

Note that when the 2023 Manufacturing schedule_ExcelForum (bigsy73).xlsx workbook is closed the link expands.

[bigsy73]

That's great, thank you so much JeteMc

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.