Auto Populate Date Ranges to Linear Calendar

Hello everyone,

What I am trying to do: I want to paste a list of maintenance inspections into a table with there expected duration. Then I would like to have them input to a linear calendar for each date in the date range (Like a project planner) This will allow me to schedule maintenance and then based on all of the maintenance that is scheduled, I can calculate my my available assets that I can use each day. I found 2 templates that each do a portion of what I am looking for and have been trying to merge them together. So now I am stuck...

Three files have been attached....

1. Weekly List Template - from MS Office Templates
2. Calendar Auto Populate Schedule - from these forums
3. 90 Day MX Outlook - The workbook I am trying to make

Any help would be greatly appreciated.

Thank you,
Andrew

That middle file is one of mine (from some time ago). In your 90-day file you will need to change the numbers in column B of the Schedule sheet to 1,2,3,4, etc. instead of 100,101,102, etc., and change the formula in C5 to this:

=IFERROR(INDEX('Task List'!$K:$K,MATCH(C$4&"_"&$B5,'Task List'!$M:$M,0)),"")

Copy the formula down and across as required.

Hope this helps.

Pete

"That middle file is one of mine (from some time ago). In your 90-day file you will need to change the numbers in column B of the Schedule sheet to 1,2,3,4, etc. instead of 100,101,102, etc., and change the formula in C5 to this:

=IFERROR(INDEX('Task List'!$K:$K,MATCH(C$4&"_"&$B5,'Task List'!$M:$M,0)),"")

Copy the formula down and across as required.

Hope this helps.

Pete"


Pete,

Your original file is amazing. Thank you the quick response. 100, 101, etc. refers to a vehicle Serial Number. Each row would represent a Vehicle, and I need the formula to fill in the cells based on the inspections for that specific vehicle for the specified date range.

It's getting a bit late here now (2:15am), so I need to get some sleep. I'll take another look tomorrow.

Pete

I've amended your 90-day file to hopefully give you what you want.

I've amended the Task List sheet by inserting a new column K and using this formula in K3:

=IF(OR(J3="",J3="-"),"",INDEX(B:B,J3)&"")

and also changed the formula in N3 to this:

=IF(M3="","",M3&"_"&K3&"_"&COUNTIFS(M$3:M3,M3,K$3:K3,K3))

Obviously, both formulae are copied down.

Then in the Schedule sheet I've changed the formula in C5 to this:

=IFERROR(INDEX('Task List'!$L:$L,MATCH(C$4&"_"&$B5&"_1",'Task List'!$N:$N,0)),"")&
IFERROR(CHAR(10)&INDEX('Task List'!$L:$L,MATCH(C$4&"_"&$B5&"_2",'Task List'!$N:$N,0)),"")&
IFERROR(CHAR(10)&INDEX('Task List'!$L:$L,MATCH(C$4&"_"&$B5&"_3",'Task List'!$N:$N,0)),"")&
IFERROR(CHAR(10)&INDEX('Task List'!$L:$L,MATCH(C$4&"_"&$B5&"_4",'Task List'!$N:$N,0)),"")&
IFERROR(CHAR(10)&INDEX('Task List'!$L:$L,MATCH(C$4&"_"&$B5&"_5",'Task List'!$N:$N,0)),"")

You can see the similarity between the terms, as the only thing that changes is the number shown in red. This will allow up to 5 events on the same day for the same vehicle to be displayed, and the formula needs to be copied across and down to complete your display.

Hope this helps.

Pete

Originally Posted by Pete_UK

I've amended your 90-day file to hopefully give you what you want.

I've amended the Task List sheet by inserting a new column K and using this formula in K3:

=IF(OR(J3="",J3="-"),"",INDEX(B:B,J3)&"")

and also changed the formula in N3 to this:

=IF(M3="","",M3&"_"&K3&"_"&COUNTIFS(M$3:M3,M3,K$3:K3,K3))

Obviously, both formulae are copied down.

Then in the Schedule sheet I've changed the formula in C5 to this:

=IFERROR(INDEX('Task List'!$L:$L,MATCH(C$4&"_"&$B5&"_1",'Task List'!$N:$N,0)),"")&
IFERROR(CHAR(10)&INDEX('Task List'!$L:$L,MATCH(C$4&"_"&$B5&"_2",'Task List'!$N:$N,0)),"")&
IFERROR(CHAR(10)&INDEX('Task List'!$L:$L,MATCH(C$4&"_"&$B5&"_3",'Task List'!$N:$N,0)),"")&
IFERROR(CHAR(10)&INDEX('Task List'!$L:$L,MATCH(C$4&"_"&$B5&"_4",'Task List'!$N:$N,0)),"")&
IFERROR(CHAR(10)&INDEX('Task List'!$L:$L,MATCH(C$4&"_"&$B5&"_5",'Task List'!$N:$N,0)),"")

You can see the similarity between the terms, as the only thing that changes is the number shown in red. This will allow up to 5 events on the same day for the same vehicle to be displayed, and the formula needs to be copied across and down to complete your display.

Hope this helps.

Pete

This does more than just help, this is exactly what I needed to do. Thank you so much!

Andrew

That's good to hear, Andrew - happy to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

As you are relatively new to the forum, you might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Also, you don't need to quote whole posts, especially if you are replying directly to another one, as it is just clutter - use the Reply button rather than Reply With Quote.

Pete