SOLVED

Help with formulas to run data on caseloads

%3CLINGO-SUB%20id%3D%22lingo-sub-2737915%22%20slang%3D%22en-US%22%3EHelp%20with%20formulas%20to%20run%20data%20on%20caseloads%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2737915%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EThis%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Bwill%20a%20spreadsheet%20that%20will%20have%20all%20our%20schools%20in%20district%2C%20with%20therapist%2C%20and%20student%20names.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ETherapists%20will%20create%20school%20worksheets.%26nbsp%3B%26nbsp%3BKeep%20in%20mind%2C%20some%20therapists%20work%20at%20multiple%20sites%20so%20their%20name%20will%20appear%20on%20multiple%20school%20worksheets.%26nbsp%3B%26nbsp%3BWhat%20we%20would%20like%20to%20do%20is%20run%20a%20query%20of%20a%20therapist%20throughout%20the%20workbook%20and%20have%20it%20insert%20that%20data%20into%20other%20worksheets%20titled%20by%20therapist%20name.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EFor%20example%2C%20in%20the%20sheets%20we%20have%20created%20here%20you'll%20see%20School%20B%20and%20Itinerant%20with%20Therapist%20B%20as%20the%20therapist%26nbsp%3B%26nbsp%3Bat%20both%20sites.%20We'd%20like%20to%20mine%20all%20the%20worksheets%20and%20where%20it%20sees%20the%20name%20%22Therapist%20B%22%2C%20insert%20the%20rows%20into%20the%20worksheet%20title%20%22Therapist%20B%22.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWe%20need%20this%20information%20to%20track%20caseload%20size%20per%20therapist%2C%20how%20many%20sites%20we%20serve%2C%20and%20how%20many%20kids%20we%20serve%20total.%20This%20information%20is%20provided%20to%20our%20adminstration%20team%20to%20help%20with%20staffing.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2737915%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2740063%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20formulas%20to%20run%20data%20on%20caseloads%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2740063%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1151454%22%20target%3D%22_blank%22%3E%40SLP2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20taken%20the%20liberty%20to%20demonstrate%20how%20a%20single%20comprehensive%20database%20can%20serve%20as%20the%20basis%20for%20summary%20reports%20on%20individual%20therapists%20as%20well%20as%20summaries%20of%20each%20location's%20activity.%20Although%20this%20may%20be%20counter-intuitive%2C%20having%20separate%20sheets%20for%20each%20location%20actually%20gets%20in%20the%20way--or%20makes%20it%20harder--to%20do%20what%20you%20were%20wanting%20to%20do%2C%20summarize%20activity%20of%20a%20given%20therapist%20across%20multiple%20locations.%20We%20do%20that%20because%20the%20single%20sheet%20per%20location%20is%20the%20way%20we'd%20be%20doing%20it%20on%20paper%2C%20perhaps%2C%20but%20unless%20there's%20a%20compelling%20reason%20to%20continue%20that%20paradigm%2C%20you'll%20find%20that%20Excel%20can%20do%20a%20great%20job%20of%20sifting%20and%20sorting%20data%20from%20a%20single%20comprehensive%20database%20quite%20readily.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20based%20on%20the%20assumption%20that%20you%20were%20giving%20access%20to%20the%20entire%20workbook%20to%20each%20therapist%20anyway%2C%20that%20there%20is%20no%20reason%20why%20they%20won't%20see%20(or%20can't%20see)%20one%20another's%20activity.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3EIt%20also%20uses%20two%20new%20functions--%3CSTRONG%3EUNIQUE%3C%2FSTRONG%3E%20and%20%3CSTRONG%3EFILTER%3C%2FSTRONG%3E--that%20do%20require%20the%20most%20recent%20version%20of%20Excel.%20If%20you%20don't%20have%20that%20installed%2C%20then%20these%20will%20not%20work.%3C%2FU%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20four%20sheets%20in%20this%20modified%20workbook%20are%20new%20(I%20did%20nothing%20to%20your%20originals%20other%20than%20copy%20from%20them%20in%20the%20creation%20of%20these%20new%20ones)%3C%2FP%3E%3CUL%3E%3CLI%3E%3CSTRONG%3EComprehensive%20DB%3C%2FSTRONG%3E%20is%20the%20comprehensive%20data%20(copied%20from%20each%20of%20your%20individual%20school%20and%20itinerant%20lists)%3C%2FLI%3E%3CLI%3E%3CSTRONG%3EIndiv%20Therapist%20Rept%3C%2FSTRONG%3E%20is%20where%20you%20can%20select%20the%20therapist%20for%20whom%20you%20want%20to%20see%20the%20summary%20(there%20are%20two%20yellow%20cells%20at%20the%20top%20of%20the%20screen%3B%20in%20cell%20B2%20there's%20a%20dropdown%20list%20where%20you%20select%20the%20one%20to%20be%20summarized%3C%2FLI%3E%3CLI%3E%3CSTRONG%3EIndiv%20Location%20Rept%3C%2FSTRONG%3E%20does%20the%20same%20for%20location%2C%20and%20the%20formulas%20you'd%20written%20work%20to%20summarize%20whichever%20location%20you%20select%3C%2FLI%3E%3CLI%3E%3CSTRONG%3ETables%3C%2FSTRONG%3E%20is%20just%20a%20%22behind%20the%20scenes%22%20sheet%20that%20serves%20as%20the%20basis%20for%20the%20drop%20down%20(AKA%20data%20validation)%20%26nbsp%3Blists%20in%20the%20two%20Reporting%20sheets.%20Those%20formulas%20are%20dynamic%20so%20the%20lists%20will%20grow%20as%20you%20add%20names%20of%20therapists%2C%20or%20other%20locations%2C%20in%20that%20Comprehensive%20DBs%20table.%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2752578%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20formulas%20to%20run%20data%20on%20caseloads%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2752578%22%20slang%3D%22en-US%22%3EThank%20you!%20This%20is%20extremely%20helpful!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2752634%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20formulas%20to%20run%20data%20on%20caseloads%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2752634%22%20slang%3D%22en-US%22%3EYou're%20very%20welcome.%3C%2FLINGO-BODY%3E
New Contributor

This will a spreadsheet that will have all our schools in district, with therapist, and student names.

 

Therapists will create school worksheets.  Keep in mind, some therapists work at multiple sites so their name will appear on multiple school worksheets.  What we would like to do is run a query of a therapist throughout the workbook and have it insert that data into other worksheets titled by therapist name.

 

For example, in the sheets we have created here you'll see School B and Itinerant with Therapist B as the therapist  at both sites. We'd like to mine all the worksheets and where it sees the name "Therapist B", insert the rows into the worksheet title "Therapist B". 

 

We need this information to track caseload size per therapist, how many sites we serve, and how many kids we serve total. This information is provided to our adminstration team to help with staffing. 

Thanks.

6 Replies
best response confirmed by SLP2021 (New Contributor)
Solution

@SLP2021 

 

I've taken the liberty to demonstrate how a single comprehensive database can serve as the basis for summary reports on individual therapists as well as summaries of each location's activity. Although this may be counter-intuitive, having separate sheets for each location actually gets in the way--or makes it harder--to do what you were wanting to do, summarize activity of a given therapist across multiple locations. We do that because the single sheet per location is the way we'd be doing it on paper, perhaps, but unless there's a compelling reason to continue that paradigm, you'll find that Excel can do a great job of sifting and sorting data from a single comprehensive database quite readily.

 

This is based on the assumption that you were giving access to the entire workbook to each therapist anyway, that there is no reason why they won't see (or can't see) one another's activity.

 

It also uses two new functions--UNIQUE and FILTER--that do require the most recent version of Excel. If you don't have that installed, then these will not work.

 

The first four sheets in this modified workbook are new (I did nothing to your originals other than copy from them in the creation of these new ones)

  • Comprehensive DB is the comprehensive data (copied from each of your individual school and itinerant lists)
  • Indiv Therapist Rept is where you can select the therapist for whom you want to see the summary (there are two yellow cells at the top of the screen; in cell B2 there's a dropdown list where you select the one to be summarized
  • Indiv Location Rept does the same for location, and the formulas you'd written work to summarize whichever location you select
  • Tables is just a "behind the scenes" sheet that serves as the basis for the drop down (AKA data validation)  lists in the two Reporting sheets. Those formulas are dynamic so the lists will grow as you add names of therapists, or other locations, in that Comprehensive DBs table.
Thank you! This is extremely helpful!
You're very welcome.

@mathetes I'm struggling to understand how you got the formula attached, specifically the (Table1,Table1[Service Provider] part. Thanks! 

@SLP2021 

 

I'm struggling to understand what you mean by "how [I] got the formula"--

 

Table1 is the name of the table that comprises the comprehensive database.

 

Here's a video from Microsoft that explains the FILTER function. It happens to be this video that introduced me to the power of that function. So if that's what you mean--how I learned of it. https://www.youtube.com/watch?v=9I9DtFOVPIg

 

Come back with a more clear explanation of what you still don't understand and I'm happy to help.

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...
I think I have it figured out. The video really helped! Thanks!