Using a time-frame, reference/paste a cell value

I am working on a project to benefit a non-profit daycare - they would like an easy way to schedule clients/see how many children are in the facility at a given time.

I have a table set up where staff can enter the client's name, number of children, and children's arrival/departure times. For each client, I would like the number of children they will be leaving at the center applied on a corresponding table based on the arrival/departure time.

For example: If client 1 has 2 children and will be leaving them from 8 am to 12 pm, on the corresponding table( to the left in my spreadsheet), under the "Client 1" column, I would like the number 2 to auto-populate from 8 am to 12 pm.

I have tried a number of formulas - including VLOOKUP and IF(AND) and can't seem to get the combination just right. Any help would be greatly appreciated!!!

Daycare Help.xlsx

Hi - thanks for the response! I have attached what I would like it to look like (but calculated with a formula instead of hardcoded, of course)

Daycare Help.xlsx

Hey all - I figured out the issue. THANK YOU FROM THE BOTTOM OF MY HEART! I have been working on this for months - so glad to have it figured out!

Hey - thank you gak67! I have tried to figure out what you did to get that - so I can replicate it...and for whatever reason when I added new data to column three on the input table, it didn't show up in the count table (on the left)...but for the first two it worked like a charm.

Maybe its a copying error on my part?

Thanks again to everyone who has replied!

Hey man ,
got the solution..
check the attached file...




Say thank, click *

Hi Vikas ,

i think u r confused , the requirement is to transfer the data in column O to B-K with respect to time in Column Q & R and client in N to Range B3-K39
Punnam