Help please

[tly0227]

Background information: I run a program at a community college that gives students extra credit in one of their classes if they complete a specific number of hours of civic and academic engagement activities during a semester. The requirements vary slightly for students in a government class than any other class. I am trying to set up a total hours spreadsheet that does a couple different things. I already have the breakdown of hours (volunteer work, tutoring, workshops), plus a column that totals it all together. That column is then set up with conditional formatting to turn a specific color based on how many hours they have at any given point. The breakdown of hours pulls the individual total from specific sheet (tutoring, workshops, etc) and the total column just sums from those 3. What I need to do is have additional columns that turn green if they have met all the requirements and have therefore completed the program.

For my government students, they have to complete 10 hours volunteer work, 12 hours tutoring, and 3 hours workshops for a total of 25. For everyone else, they have to have a total of 25, but 15 of those hours is their choice and 10 has to be volunteer work. I was thinking I can have 1 column that indicates "Yes" or "No" if they are a government student (this information is on a different sheet. Another column needs to have a formula that enters "Yes" if they are a government student, have 10 hours civic, 12 hours tutoring, and 3 hours workshop. That same column would need to be "Yes" if they are not a government student but have 10 hours volunteer with a overall total of 25.

Here are the cell names for each section. Headings are row 1, and column A-C is the student information...so the first row of actual data is 2.

Column D: Tutoring
Column E: Workshops
Column F: Volunteer
Column G: Total
Column H: Where it should indicate whether they are a government student or not. This information will come from column Q on a different sheet called 'Student Information'. I can name that range if it makes it easier. If they are a government student, column Q for them will say 'R. Randle'...otherwise it has a different name.
Column I: Where it should indicate if they have met the requirements or not.

What I need help with is a formula for Column H and I. I know all the data that needs to go into the formula, I am just at a complete loss as to how to set it up. Any help is greatly appreciated.

 

[par60056]

I will make a stab at what you want. It may be totally off but may help you figure out what you want.

H: =if('othersheet'<other sheet="">!Q#<rownumber>="R. Randle","Government","Not Governtment")
I: =If(H#="Government",formula for government<formula for="" government="" requirement="">,formula for not government<formula for="" not="" government="" requirement="">)</formula></formula></rownumber></other>

 

[tly0227]

So I got column H figured out, it was easier to just manually input it since it's not information that is going to change. So for column I, it needs to say "Yes" if H2=Yes and D2 is greater than or equal to 12, E2 is greater than or equal to 3, and F2 is greater than or equal to 10. It needs to say "Yes" if H2=No and F2 is greater than or equal to 10 and G2 is greater than or equal to 25. If it does not meet either of these, then it needs to say "No".

I haven't been able to figure out the formula for any of this.

 

[par60056]

I'll take another stab.

i2: =if(h2="Yes",if(and(d2>=12,e2>=3,F2>=10),"Yes","No"),if(and(f2>=10,G2>=25),"Yes","No"))

 

[tly0227]

I think that maybe worked. I don't have anyone that has actually met the requirements yet, but I did a couple tests on it and it looks like it works. Thanks!