Create a calendar from dates start and end

JAMIN85 

I came in to write out an explanation of how that formula works and realized there is a mistake in it, such that it worked well for that first example, but probably would not continue to work for all of the different ref items. A revised formula in a new spreadsheet is attached. 

So here's a revised formula.

=IFERROR(LET(datearray,FILTER(BASE!$M2:$M6722,BASE!$A2:$A6722=$D5),status,MATCH(E$1,datearray,1),Wharray,FILTER(BASE!$L2:$L6722,BASE!$A2:$A6722=$D5),INDEX(Wharray,status)),"---")

By the way, doing this every day for so many years and so many items is a major consumer of computer power. You might want to reconsider your design, or only do a few rows at a time and then save the results as values rather than as formulas.

So let me break it down, from the inside out, explaining what the LET function does along the way.

FILTER(BASE!$M2:$M6722,BASE!$A2:$A6722=$D5)

This creates an array of the dates from your Base tab where the Ref equals what is in column D of "Calendar" -- so an array of the dates for Ref 18535, for example

LET then assigns that array to the temporary name "datearray"

FILTER(BASE!$L2:$L6722,BASE!$A2:$A6722=$D5)

Similarly, this creates an array of the data in column L (which you'll see I changed) where Ref equals column D of Calendar tab 

LET then assigns that array to the temporary name "WHarray" (for Warehouse array)

MATCH(E$1,datearray,1)

Finds the first row in dataarray that is less than the date on the top row of the Calendar tab; LET assigns that value to "status"

INDEX(Wharray,status) 

Uses status to find the word in WHarray that's in the same row -- which is why I changed those to be "Available" "Worksite" etc

the IFERROR function serves to take dates where there is no record and returns a blank (to make the spreadsheet easier to see; I initially had it return "No Rec" but that text doesn't really function helpfully.