Hey! I'm working on inter matched sheets at work to facilitate everyday uses but since I'm barely starting to play around with excel I could really use an helping hand!

Unfortunately for now my formulas end up long and uncertain at best... And seeing how often I must repeat them while changing a single digit each time,(numerous time per formula) I was wondering if anyone had a way to make them more efficient? (I need to remake it about 40+ times varying from g4:g42 give or take...) and to make matters harder, I'm currently not at work and so not even have excel with me to test these out more thoroughly...

First I guess I should explain the 2 sheets.
One is a "client's list"
One is a "client's departure times"

Now since these lists are updated daily I'd like for the formulas to stay good and not have to modify them as long as the sheets remain with the same name! (Or if there is a way to make it so they can all be altered easily to simply target a different sheet? Since we do keep them all for statistics purposes... I highly doubt it but who knows... Also if not possible to make the second page follow and retain each time with a duplicate, it might not be too big an issue that the second sheet simply regulates itself to the other daily sheet since that one must be printed daily anyway...)

This first one is meant to determine if the client is leaving on this day and if such I require it to write in the case the estimated time of departure ("cs" and "CI" leave 48h after arrival while "ac" leave 36h after but neither ever after 22:00) the last bit of it is for the clients to be put automatically as well as an x(or hook) if for any reasons their departure was delayed beyond their 36/48h so that we know they are technically the firsts to leave(and the final " " is so when no one is using that particular space or for particular cases that do not fit in the 3 categories nothing shows up)

=if(and(I5="a10-2",and(H5<"0.9166",
or(g5="cs",g5="ci"))),"H5",if(and(I5="A10-1",and(and(H5<"0.4166",g5="ac")),"g3+0.5",if(A10>I5,"x"," ")

A10=current date;I5=date of arrival;G5=classification;H5=time of arrival;0.9166=22:00 and 0.4166=10:00( give or take a minute)


Secondly I need a formula to put names on the second list but, only specific ones as many clients that leave are not taken into account on the second page...

=if(or(c4="bd","ma","mi","sim","bb"),"b4"," ")

Or

=if(or(c4="gy","na","pp","si"," ")," ","b4"
(This one using the 4 possibilities that may stay well beyond then 48 hours, as well as the blank, and as such shouldn't make it to the second sheet)

Basically this one is simply that I need for the second sheet to add the names in row B but only those that have any of those 5 possibilities in row C. (I'm guessing I have to add c4= after each comma? And maybe add "or(" each time as well?) again result needs either be blank if not one of the 5s or the name written in that tile.

Last formula I need to twink up is

=sumifs(c3:c42,c3:c42,"bd",c3:c42,"ma",c3:c42,"mi",c3:c42,"bb",c3:c42,"sim")

Which if correct should calculate for me the amount of clients in that set of categories? (Or more precisely add up the number of times these 5 choices were made?)

Also, I know before just about every case number I'll need to add the sheet name but like I said, I'm currently not at work and as such I cannot add them... I'll definitively add them as soon as I can use the formulas for good!

In hopes a kind and skillful soul will help me make these right ;-)