Conditional Cells based on Gender and Using forms to create sheets?

[Stretch93514]

I am trying to design a spreadsheet to help our medical coordinator keep track of when the last annual appointment was for the individuals we serve.

I had a few ideas and i wasn't sure if they're possible or even how to do them. So here I go, any help would be much appreciated.

1.  I thought about using Forms so she could enter the data via her laptop or phone. i would need it to do the 2 following things.
   1. when initially setting up, create a new sheet for unique individuals or put data in a specific sheet.
   2. after initial set up, overwrite previous data with new updated information. for example if the last annual physical was July 2, 2020 and the individual gets another physical on July 13, 2021 and she fills out the form can it replace the July 2, 2021 date with the new date?
2. Just a thought but not crucial... Under the specialty Procedures area are things that are gender specific.  I thought it might be nice if, only the ones for a specific gender showed.
3. Every 3 months, we need to send a report showing this information for each individual.  I just was curious as to the best way to approach this?  I am totally new to this idea. I will be watching videos on this as well
4. I would also like for it to automatically let her know when an individuals annual appointments are approaching.  say 2 months before for  standard, but 3 months before for specialty.
5. Right now I am envisioning Using a separate sheet for each individual.  Might it be better to have 1 sheet with the individuals names on the left and then columns or tables for the different appointment types and dates.

I am trying to convince her that this will be a better more convenient option than a paper version.

Our company is too small(cheap) to provide Microsoft 365 for business for us.  I am using my Personal Paid version and I believe she has a student version that she uses, if that makes any difference.

thanks for taking a look at this.

1.  

[#2]

@Stretch93514 

This medication chart template tracks dosage, instructions, purpose, side effects, physician, pharmacy, and other details.

You can change this template as you wish.

If you then need further help, we are at your disposal.

This means that you can also try out your colleagues' other offices to see whether the file works everywhere.

Here is the Microsoft link with the online templates

Microsoft Templates

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#3]

@Stretch93514 

There is a lot here but what I would recommend for you to get started is to think of setting up the sheets as 1 for patient entry and another for appointment entries.  So the Patient sheet would have 1 row for each patient and all their 'fixed' information and the Appointment sheet can have an entry for every appointment with the date, patient ID, type, and any other relevant info.

Then you can use pivot tables and power pivot tools to create all kinds of reports if you want.

As in the attached example you can also have some formulas on the patient sheet to extract the latest/last appointment of each type.

I hope this concept gets you going and maybe we can tackle additional questions as they come up.