I still don't know if this is possible.
So far the excel right now it works like this:
5 possible Abbreviations for any Majors/Minors can be entered from P4:Q10 which are then combined in every possible way, to account for any classes.
For example, 1 class could account for both Major1 & Major2, or Major1 & Minor 1, etc…
These options are held in a dynamic drop down box that a user can select items out of into the ClassMajorMinorList (Column F). Adjacent to the class list column are the HonorPoints (Column H) and Credits (Column I) columns that come from a separate sheet along with the class names (Column G).
I'm looking for a dynamic formula that extracts data from a different column in the same row (either HonorPoints or Credits--I will need both for the GPA), based on whether or not ClassMajorMinorList (Column F) row is identical to one of the 13 or 14 combinations that affect each Major/Minor GPA calculation. There are 5 separate possible GPA calculations, that must use data from both HonorPoints & Credits in the same row as the drop down ClassMajorMinorList. The first three calculations should use any HonorPoints/Credits that are in the same row as 13 different criteria, the last two have 14.
Screen Shot 2014-07-31 at 1.30.49 AM.png
Here is the attached document.*
Universal_GPA_MajorMinor_TEMPLATE.xlsx
Thanks for taking the time to look at this huge complicated project!
Red
Edit: Figured it out.
Used
=IF(ISNA(MATCH(MajorMinorGPAs!$G8,$P$8:$P$20,FALSE)),"",IF($I8="","",VLOOKUP(MajorMinorGPAs!$G8,MajorMinorGPAs!$G8:$H8,2,FALSE)))
to match the drop down box row to the 13 conditions
change the column number to 3 for credits, 2 for honor points. Twas fun to figure out.
Bookmarks