Forum Discussion
Help for auto returning a value between worksheets...confused teacher
I am creating a document for our teachers to use so that each 9 weeks grading period they can go into a worksheet and check off who qualified for honor roll, banner roll, Os in conduct, and perfect attendance (I'm a teacher :) )
I created the workbook and put each nine weeks period on a different worksheet.
As it is now, the teachers have the option next to each name, to choose either "x" or leave it blank. (the "x" means the student received that honor). ---this can be changed if you have a better way of doing it!
So...on the fifth worksheet, I want the document to auto-calculate if the student qualified for the "overall" award...meaning they received it every nine weeks. This would currently show as an "x" next to their name in the appropriate column on each of the four previous worksheets.
HOW do I put a formula that will automatically say yes or no if they qualify? I tried the IF function, and just couldn't figure it out. Any guidance is appreciated.
Thank you in advance!!!!!
I attached the file to help you out.
Hello Kelly,
Try this. I took the liberty of feeding the names from sheet one to all of the other sheets, so you only have to add the names once. I did this because it directly references back to the cells on the other sheets, it doesn't care if the name to the left is the same. It doesn't allow for adding someone mid-year. You would just add them to the first empty slot on the first sheet, and track from there. It's fairly simple, but potentially easy to break as well. Each sheet is protected, with the cells with formulas locked, but there is no password, so you can just go to the Review tab, and click unprotect sheet. Let me know if it will work for you.
6 Replies
Hi
In D3:
=IF('1st 9 Weeks'!D3&'2nd 9 Weeks'!D3&'3rd 9 Weeks'!D3&'4th 9 Weeks'!D3="xxxx","x","")Copy down and across.
- this is great! Works like a charm. thank you.
Hello Kelly,
Try this. I took the liberty of feeding the names from sheet one to all of the other sheets, so you only have to add the names once. I did this because it directly references back to the cells on the other sheets, it doesn't care if the name to the left is the same. It doesn't allow for adding someone mid-year. You would just add them to the first empty slot on the first sheet, and track from there. It's fairly simple, but potentially easy to break as well. Each sheet is protected, with the cells with formulas locked, but there is no password, so you can just go to the Review tab, and click unprotect sheet. Let me know if it will work for you.
- This is awesome! I wish I was as savvy as you!
One more query I'd love to fix if possible....for honor roll/banner roll.....honor means As and Bs, banner means all As. So, if a student has honor roll three times, and banner roll once (or any combination), as long as they AT LEAST make honor roll all year, they will qualify for honor roll.
So, if a kid has x in three honor roll columns, and one banner roll, the last sheet won't show a YES in the honor roll section, but it should. So how can we auto-calculate that? Is it possible?Kelly,
This should work for you. If there is an x in either Honor roll or Banner roll, on all 4 sheets, the overall sheet displays Yes for Honor roll. If they achieve Banner roll on all 4 sheets, Honor roll and Banner roll will both display Yes
