Using the Indirect Function with Drop Down Lists and Multiple worksheets

[woodie]

I have a workbook with the following spreadsheets: Summary, and Jul18, Aug18, Sep18 (and rest of the months going to May19). On the Summary Spreadsheet, it looks like this:


A1 - Month (drop down list)
A2 - Student (drop down list)
A5 - Number of Assignments
A7 - Number Completed
A9 - % Completed


I want to add formula's that when I select a month from the drop down list, then a student from the drop down list, it will go to the worksheet for that month and determine the number of assignments (in B5), the number completed (in B7) and the % Completed for that particular student (in A9.

For the monthly spreadsheets, the names of the students are in column B and the Assignments are listed in Column F and Completed Assignment (y/n) is listed in Column L.

There are 3 types of assignments (1,5 or 24) so if I could break them down by type, that would be an added bonus.

I think that I should be using the Indirect Function, but as I novice, I have no idea how to get the desired result....or how to begin for that matter.

Any assistance that anyone can provide will be greatly appreciated.

 

[Ron Coderre]

What's your reasoning for not having all data for all months in one sheet?
That would allow you to easily reference the data you need without resorting to complicated, volatile formulas.