posting dynamic date in individual spreedsheets to overview spreedsheet

[craig.norris]

I am not an excel expert, and have limited experience with the program, so please be gentle.

I am trying to build a workbook to track patient treatments. My overview sheet needs to list the last treatment received per patient which is listed in column a of each patient's individual sheet dynamically so I can review the history of treatments as well. I have tried to create a dynamic list, but it is not functioning correctly. I was using : =OFFSET(txdate,1,0,COUNTA('patient, name'!$A:$A),1) , where column A ( the named range "txdate") lists the treatment dates in succession and should be updated automatically when a new treatment is done.

Any help would be appreciated. If clarification is needed please let me know.

[AndyLitch]

I can do much more than that if you like ... and i'd like to help...
A reliable patients database would be a worthwhile mini project...

How many patients roughly & do you want their treatment histories time & date stamped ?
Do you have any objections to VBA rather than worksheet formulas ?

[craig.norris]

Book1 template.xlsxI have no objections at all to help! Thanks! I have tried to attach a copy of what I am trying to do so you can see it and maybe understand. Each patient has their own spreadsheet that reflects each date they received treatment. Each initial treatment is with 15 pellets. At 1 month there is a blood draw, it tells me how many pellets that the patient will need with each treatment. At 3 months from initial treatment there is another blood draw that tells me how often they need additional treatments. These two formulas I did figure out, but feel free to make them better/more reliable. I need the overview sheet to show me when the last treatment was given and when the next treatment should be given. The next treatment is based on the second blood draw- either every 3, 4 or 5 months, after this has already been established, treatment should remain on this schedule unless there are physiological changes that affect the amount of pellets in the treatment (levels too high need fewer, levels too low need more). I would like it to flag patients that are non-compliant with getting their blood work on time so i can call them and remind them to get it done, otherwise the patient and I have to start all over again in the process. Also every six months of continued treatments needs another blood draw to ensure we are within therapeutic range and there are no adverse events creeping up on us.

I hope i haven't scared you off. I could really use the help. If you have questions or need more info (I am sure I left something out) let me know.

[AndyLitch]

I'll get back to you with a first revision asap

[craig.norris]

Thank You, you are a life saver!

[AndyLitch]

Have a look at that and tell me what you think..

Scheduling Ex rev A.xlsm

[craig.norris]

I think you are thinking of what I want, but when I copy and paste your formulas into my sample workbook they give me the #Ref! caution. It says it is because of cell reference. I studied your formula and adjusted it for the workbook template I posted (i.e. A4 for A11) but the "!" and the B column references are lost to me. My column B is First names, which I had hidden and forgot to reopen in the sample workbook. When I expanded what you sent me it did show the first name column so I left that part alone. I am using 2007 and I noticed you are listed as a 2013 user, could this have something to do with it? I like the scheduling sheet which is something i had not thought of because I "fit" these appointments in while seeing my other patient load, but I often have done 2 or 3 a day, and the goal is up to 5. I guess it would be the next step though. I took the program over from another provider that no longer works for us and was bitter about leaving, she did not leave any of her manual notes on the topic so I am trying to get caught up.

[AndyLitch]

I think the easiest way around the #Ref problem (depending on how many patients you have) would be to transfer your patient data into the workbook I sent you.... If there is too much we could fix the problem instead..
I could redo the scheduling sheet with VBA so it will capture all appointments - that's easy enough....

Let me know which way you prefer to go

[craig.norris]

I currently have around 50 patients (goal is 200-400). The majority have 1-3 treatments already, but some have 4 or even 5. Some have lab work correct, some do not. A friend of mind mentioned to me that it might be easier/less confusing to use the overview for all the data input and have it post the new data into a dynamic date list (tx Date), or relevant other field on the patient's individual sheets, and tally the number of treatments and input that number into the "# tx" column. It makes sense because it is the main sheet I will be looking at and using the most. Once we get the body running then we can add the scheduling sheet you created and we will be golden!

I created an exact replica of the workbook and will attach it to this post. It is the 0.5 version, the 1.0 is what I sent you earlier (I know it is backwards, it was suppose to be 1.5 but I hit enter too quick) It better clarifies what I am needing to do and how the fields relate to each other with comments on the headers. I wish I could attach the original, but because of patient's rights and privacy, I cannot.

Book0.5 template.xlsx

[AndyLitch]

At the heart of any good database is a search engine.. Reports can easily be generated from any search.. See what you think.


Consultations.xlsm

[craig.norris]

It looks good. Thanks. Still need the functionality of the date lists though.