If Then/And Formula to Show Dock Usage Visually

Looking at what the best way to build out a formula that will carry over to another sheet to show a visual representation of my dock availability is based on the table of data in the first sheet.

Sheet 1 will be the master data file that will continuously be updated as trailers check-in/out throughout the week.

Sheet 2 will be the visual representation that shows that a trailer is being used at a specific dock door.

In my mind, the first step would need to disregard any load(line) that has a status of "unloaded" as this would be the indicator that this trailer is no longer on site.
Second step would to look at the status column in look for the status of "Storage" or "WIP"
Third Step would need to identify what dock door # is being used to transpose to the correct visual dock door on Sheet 2.

On the visual dock diagram, I want to have text carry over from the data table in sheet 1 so I can tell what door is being occupied, what commodity, trailer # and the date of arrival. (I have this currently typed in for a visual representation for the "end-goal") then have a conditional formula that will format the "dock" (cells) that have text with Live Highlight Green, text with Drop Highlight Orange and the cells that do not have text turn Red.

Negative dock will only ever have one occupant.

Correct, only 1 output per dock. Once the load is completed, the status will be updated in the data table to "unloaded" which then should indicate that the dock is available for use.

Great, thank you.

A few questions as I play around with this.
1) Is their a way to auto-run the Macro. For Example, when I add a load or turn a load to "unloaded" can that reflect more "real-time" on the Dock Visual Sheet. Currently I have to click View Macros, then Run; for the data to transfer over
2) When a load is marked as Unloaded, it will stay on the visual chart unless I delete the data from that cell or have a new load that is added to the Data Log Table to replace it.
3) If I needed to add more docks or change the names, do I need to change the code in the Macro?

Thanks again!

I greatly appreciate your work!