Sep 09 2021 01:07 PM
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.
Sep 10 2021 07:13 AM
Solution
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)
Sep 15 2021 05:41 AM
Sep 20 2021 12:53 PM
@mathetes I'm struggling to understand how you got the formula attached, specifically the (Table1,Table1[Service Provider] part. Thanks!
Sep 20 2021 01:32 PM
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.
Sep 24 2021 05:29 AM
Sep 10 2021 07:13 AM
Solution
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)