Managing and syncing three different excel files

[jvnig026]

Hello, everybody! I need some help and if anybody can at least point me in the right direction I would be eternally grateful.

I work at a rehabilitation hospital. I am in charge of making the therapy schedule for all the patients each and every day. It is a time consuming process with a lot of variables. I'm trying to make this job a little easier for me so I have more time to work with the therapists and their patients, which is what I truly enjoy about the job.

The therapy schedule consists of three separate files. The two most important files are what we call the landscapes, one for physical therapy and one for occupational therapy. They are both set up in a similar fashion. The therapists are listed on the x axis and the times are listed on the y axis. In each cell, I input the patient's last name. Most of the scheduling is done on the landscapes. I get slowed down when I get to the third file, which is what I pass out to much of the hospital staff. It consists of the patients listed on the y axis and the times listed on the x axis. In each cell, I note what kind of therapy they have and with which therapist. I lose a lot of time transcribing what I scheduled on the landscapes to the actual schedule. I also occasionally make mistakes transferring the information. I do a lot of cut and paste and I sometimes lose track.

Is it possible to have the information on the two landscapes automatically synced with the schedule? If so, can anybody give me a lead on where to start? If it isn't possible, does anybody have any ideas on how to better manage these three files? I am proficient with a computer, but I don't have a programming background.

Thanks, everybody!

[jvnig026]

Maybe an example would help to illustrate my problem?

[MarvinP]

Hi jvnig026 and welcome to the forum,

I know you are looking for an Excel answer but look at this
http://www.youtube.com/watch?v=bLG6c...eature=related
I'm imagining a PT calendar and an OT calendar. Then the patients can be in a contacts file. Ok - enough for using Outlook... but????

Looking at your example sheet you have both OT and PT on Sheet2. I thought you had 3 sheets.

There is a formula that works for what your example is but.... Give a better example and get a better answer. See the attached.

I kind of cheated on the table I made on Sheet2 as I copied all the patients from both PT and OT to a single column and then deleted duplicates (a feature in 2007 but not in 2003). Then I did a double If statement with absolute and relative ranges formula .... See attached.