Matching data between two sheets

[Thedarkrose]

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 ;-)

[FDibbins]

Hi, welcome to the forum

That sure was a lot of typing, and I got lost about 1/2 way through

I think we need to take this in small steps, and the 1st step would be for you to attach a small sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Without seeing any data to test it on, it looks like you can probably simplify your 1st formula by removing some of those exttra AND's...
Also, if you are refrering to cells with references like "H5", you need to remove the ""
=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")),=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"," ")
,if(A10>I5,"x"," ")
...
=if(and(I5=a10-2,H5<0.9166,or(g5="cs",g5="ci")),H5,if(and(I5=A10-1,and(H5<0.4166,g5="ac"),g3+0.5,if(A10>I5,"x"," ")

[Thedarkrose]

Sweet thanks! I'll try that as soon as I get to work, I'll try my formulas out too and see what happens, afterwards I'll try to share either screenshots or an actual copy of what I'm trying to perfect! There's no personal data in them yet and I'll include fictive names but it should most definitively make things clearer... And yeah, sorry, I type a lot >_< I was hoping my overtypingness would actually make things clearer but I can definitively see how it would get more confusing...

Thanks again, I'm off to work soon so of it's not too busy and my work network allows access to this site I'll definitively share more! ^_^

And if I've no extra replies by then I'll just edit this post(if I can?) to prevent spamming and clustering and what not ^_^ (there I go again overtyping >_<)

[FDibbins]

All you need to do it post a new post on this same thread

[Thedarkrose]

I tried to make it as obvious as possible though most of it stayed in french. I didn't have nearly enough time tonight to work on it and modify the aspects I still wish to change but I'm hoping this should be far enough to be able to get some guidance and teaching ;-)

And to make things a tad clearer, the file "liste de patientes.xls" would get copied daily to a new tab keeping the "today" tab as the main one yet maintaining all informations of past days wetitled by the respective date.

As for "poids journaliers.xls" unless there is a way to copy the tab in increasing numbers(following a different date each time without having to reprogram it all to retain all informations) which admitedly, I highly doubt...


Thanks in advance for any help/pointers/corrected formulas you may provide! ^_^

Liste de patientes.xlsPoids journaliers.xls





*edit* I just got home and opened these up on my cell and wow... Yeah... I hope it's that my cell opens them as images because otherwise I doubt anyone can make sense of what I'm trying to so with these >_<