Look down table and add Jobs to rows on calendar (in status priority)

Hi,

I have a problem that is way beyond me. I'm actually stuck at the very start of my list of things I need to do, and things need to eventually get a lot more complicated. I'd appreciate help with even the first bit but I'll spell the whole thing out in case anyone that likes a challenge can figure that out.

(see attached file)

I have 2 columns of dates (D & E) which represent bookings (start and end dates). I need these bookings to appear in the appropriate cells on a calendar.

The formula in the cells on the calendar looks at the 2 columns and returns the description column (F) on the calendar.
I've got as far as a formula that looks at the top row only (D13 & E13) and returns the description (F13)

Please Login or Register  to view this content.

The first problem I have is to turn this into a formula that looks down the whole list and returns F for each set of dates on the table - so that all DESCRIPTIONS (F) from the list get placed at the appropriate dates on the calendar.

So that's problem 1, and I would massively appreciate any help even just with that part.


...Where things start getting complicated is that when dates of jobs overlap, I need the overlapping dates of the booking to be bumped onto a lower line of the calendar. (But only the overlapping dates). So if I have 2 jobs on 25th - 26th January, the booking further down the list gets listed on a lower row of the calendar.

Another complication is that I have a STATUS column in the table, which needs to give CONFIRMED jobs a higher priority on the calendar than PENCIL jobs. So if 2 jobs overlap, the CONFIRMED job appears on the higher calendar line, and any PENCIL jobs appear on a line below in the order that they appear on the table. CANCELLED jobs do not appear on the calendar.

Lots of respect and appreciation to anybody that takes the time to attempt to figure this out.

Thanks in advance

OK. Thanks so much. You've totally blown my mind.


It all seems to work except for one thing.

Jobs that are confirmed need to have priority over pencils even if the confirmed jobs are further down the list.

In the attached example, The JFG RR job should move to the top line as it is confirmed. Currently this only seems to happen if it is also higher in the list.


I think that the IF functions needs reprioritising so that the first condition is the STATUS, but I'm having trouble reordering it.

AMAZING!

Thank you so much.