Need a formula to automate an Indirect Cell Validation

[jrtulare185]

Hi all,

See the attached workbook for my question below. I created a workbook to track my churches’ classes and to generate a report card. My problem is found on the Report Card tab. Cells M 1-3 is where you select the Student, year, and semester; hit “run report” (auto calculation is turned off) and after about 25 seconds the report card populates the data.

The issues is, the real report has over 1000 logs which means if I selected a student I would be guessing at the year and semester. If I guessed wrong the report would generate nothing. I can do a manual Indirect Validation on a separate sheet but that’s a lot of work for each time I add new student records.

What formula can I use that, once a student is selected, the years would only show the years for that student in M2 validation and the same for the semesters. Currently, once a student is selected all possible years will show.

Any help would be appreciated.

[JBeaucaire]

This has the benefit of the M2 and M3 drop downs will ALWAYS only show the years and semester that apply. Positionally, you will even notice they go together. But the downside is you do see duplicates, but never the inappropriate entries, a fair trade off in my opinion.

I updated many of your named ranges to make them dynamic, they will expand themselves as you add more students, teachers, courses, etc.

All the heavy lifting is done in the data validation formulas themselves in M2 and M3.

[jrtulare185]

Good stuff, this will save me a lot of time. Thank you!!!

In the future, if you can figure out how to eliminate the duplicates let me know.