Counting Instances Across Sheets
I am building a dashboard for each of the colleges at my university. I originally created a Smartsheet for each college that lists all of the courses in each of the programs in the college, documenting whether or not we have a course master (or template) built for each modality (BL=blended, OL=online). These sheets are organized by course code prefixes. So, there will be a parent row with the prefix, and children for each course (and modality) with that prefix.
In this example, you will see there is one course with ECO prefix (ECO 440) that has two modalities, BL-6w and OL-6w. Both modalities have Completed course masters (see the "Development Status" column).
For this sheet, I am able to determine how many unique and active courses are in each prefix. For ECO 440, the course is "Active" (see "A" in "Active Status" column), so the # of unique and active courses in the ECO prefix is 1, and 100% of the courses (there is still just the one) that has at least one modality with a Completed course master.
HCL 301 also has two modalities, but only one has a Completed master (OL-6w). But, it is still counted as having at least one Completed master HSC 311, however, has neither a BL or OL master, so it is excluded from the total count, even though it is a unique and active course.
Anyway, I have this all figured out for prefixes.
I am now creating a new Smartsheet that lists the programs in the college, and the required courses (e.g., ECO 440) and electives (e.g., FIN 444). I want to be able to report to college deans what percentage of courses (required and electives) have at least one course master.
One of the programs is Health Care Leadership, B.S. I entered all of the Required courses. (There are no electives.) I want to write a formula(s) so that this sheet looks at every course code in the other sheet and displays the percentage of those courses that have at least one Completed master.
Just to make things more difficult, not all courses Required are in the one college's other sheet. For example some courses are offered in one college and others, mostly electives, are offered by other colleges, so they would be on a similar but different sheet.
I am not sure how to have Smartsheet look at each course code in the list of Required courses (separated by commas), then look up that course code in the other sheet, and then determine, if it finds it, if the course has at least one master (either in BL or OL). I likely will also want to have a separate columns that can also report back if each of those courses has a BL master and/or an OL master. (Deans may ask me, "How many courses in our inventory for this program have OL masters? How many have BL masters? How many have both? How many have neither?)
I think I might be able to do this if it was just once course (find ECO 440 in the sheet, report back if it has a BL, OL, or BL & OL master). But, is there an easy way to do this if the courses are in a list, separated by a comma (perhaps using hidden columns)?
Let's say the Health Care Leadership, B.S. only has 3 required courses, which are all offered by the same college (so are on the same other course sheet). In the "Percentage Required" column, it might say 2, or 66% of the courses required have at least one master. To do this, Smartsheet would reference the other sheet, search for those three course codes (which have different prefixes), and determine if either a BL or OL master is Completed. In this example, it found two courses have at least one master, one has none. So, it reports back 2, or 66% of the Required courses have course masters.
I may actually want to have THREE columns, percentage of courses with a BL master, percentage of courses with an OL master, and a third with a percentage that have both. The Deans would LOVE to have this kind of information at their fingertips.
Thanks to anyone who can guide me in a good direction!