Generate Unique ID Using Criteria and across multiple sheets

Hello Folks!

I'll jump right in. I have a training plan and I want to automatically generate class IDs. The IDs are in a specific format, and I have a formula written up to the last item I need which is the sequential order of the class by room. The syntax of the class ID is as follows Location_Room_Subject_yyyy_mm_dd_'unique number'

That unique number where I need the assistance. The number is sequential by the room. So regardless of the Location, Subject, and Date, the number will increase by one for each class as long as it is in the same room. Furthermore, I have two types of classes, we'll call them NH and T and they are located on separate sheets. I need the formula to be able to also look at the classes that are in the same room in both sheets and track the sequentially.

For example. I have the following class on the NH sheet: LOC_ONE_Ivory_2016_10_17_6 that started on 17-Oct. On the T tab I have a class that starts in the same room (ONE) on 30-Oct so it would be the next class and would be called LOC_ONE_Maroon_2016_10_30_7.

I am not the best at using words to describe my issue, so I have attached an example file with the formula as far as I'd gotten with it as well as the desired outcome. I hope it explains things a bit better. But please let me know if I can clarify further.