Hello,

I'm somewhat new to many of the advanced excel functions. However, I am a fast learner and I would appreciate ANY help/guidance any of you experts may be able to provide.

I am creating a database for a mental health clinic. This database will be used to track patients and document the provider assigned to them. The tricky part is that I would like this database to be a "smart" form of sorts, and provide assistance in assigning providers. This is where the dependent data validation comes in. The decision to assign a patient to a specific provider is dependent on MULTIPLE characteristics of both the patient and the provider. For example, in our clinic, there are several "sub clinics" (let's call them Team A, Team B, Team C, and Team D). Each subclinic has a different "subspecialty," with different providers staffing each of the subclinics. So, the specialty subclinic (as designated by the pt's diagnosis) is the primary patient characterisitic that will determine which provider they are assigned to. However, There are also provider characteristics that will determine how patients are assigned. For example, not all the providers are 100% FTE (meaning that they see patient's 100% of the time). There may be a provider that is 80% FTE, or 30% FTE (which mean, ideally, that they would be assigned relatively fewer patients when compared with full time providers).

I have the database set up so that there is a dependent connection between subspecialty and provider. Meaning, if in the subspecialty column, I choose "Team A" only the providers that work in that subspecialty will appear. I don't know where to go from here. This is not taking into account the provider characteristics such as their scheduling differentials. Can an algorithm be written that suggests the provider based subspecialty AND weighted provider workload? Is it possible to create a database where I enter a few patient characteristics (namely, subspecialty) and the sheet would then auto-fill (or "suggest") a provider that best fits the patient (subspecialty) but also keeps things as close to “even” as possible (accounting for scheduling (FTE) differentials)

Any help would be amazing!

A relative newbie,
JCS