Who Wants to Be MY HERO

[kyfung]

Hello,

I am stumped on a project I am to complete and was looking for some help with the final area. I have learned a little on some formula's but this one taking a little longer. I am creating a spreadsheet and here is what I need. I will have a list of names and grade level and I need the corresponding grade levels to have the correct graduation date.
example
(column A) (column b) (column c)
1 Name Grade Level Graduation Date
2 Mikey 10 ?????
3 Joey 08 ?????

I need to somehow enable the column for graduation date to have the correct date according to the grade entered in the grade level column. Can not figure out how to do that. The list will contain grade levels 6 thru 12 and each one have the correct graduation date entered automatically upon import information, without having to manually enter the graduation date. Vlookup was mentioned, but can not understand that. Anyway of doing it would be great. Thanks a lot for any and all help.

 

[millhouse123]

It seems like vlookup would be a good option. What you need to do to use vlookup is build a small table to the right of your data with grades in column E and graduation dates in column FThen in your worksheet type = vlookup(B1, E1:F5,2,false)

 

[kyfung]

Thank you for the response. I heard a mention of Vlookup and tried to understand that. Thanks for confirming it. How will this may the graduation column say 2014 for say a 10th grader and 2015 for 9th grader and so on? In my example, the A column would be the name, the B column would be the grade they are in and the C column would be the graduation date. I am hopeful to have the graduation date relate to the seperate grades of the names. Is that still in a way how I would do it as you described? If so, I do not see how it can possibly do it......but I do not have to understand. haha. Thanks for your comment and hope to hear back from you

 

[gnrboyd]

Here's an example of what millhouse123 has suggested. The lookup table can be put anywhere. (such as on another tab to keep it out of the way)

Keep in mind that this approach will only work for this school year and you would need to make adjustments to the table should you want to use it for the next school year. (Oh, and by the way, it doesn't take into consideration those students that may fail a grade and have to repeat a year.. ha)

Hope this helps.

Book1
	A	B	C	D	E	F	G
1	Name	GradeLevel	GraduationDate			GradeLevel	GraduationDate
2	Mikey	10	05/25/14			6	05/22/18
3	Joey	6	05/22/18			7	05/24/17
4	Sam	7	05/24/17			8	05/22/16
5	Cindy	9	05/26/15			9	05/26/15
6	Wendy	8	05/22/16			10	05/25/14
7	Tom	11	05/24/13			11	05/24/13
8	Jill	12	05/25/12			12	05/25/12
9	Sandy	12	05/25/12
10	Bob	6	05/22/18
11	Tim	7	05/24/17
Sheet2

 

[Cindy Ellis]

VLookup seems a bit more than what is needed, and it's only good for the current year.
The following formula uses 2 additional pieces of information, the final grade (assumed to be 12, but in red below in case it's something else), and the month of graduation (assumed to be June, but shown in green in case it's some other month). This way the formula will work without changes, assuming that the students' grade levels are updated by the first of the month following a graduation.
The formula (in C2) is:

=IF(MONTH(TODAY())>[B][COLOR=seagreen]6[/COLOR][/B],YEAR(TODAY())+([COLOR=red][B]12[/B][/COLOR]-B2)+1,YEAR(TODAY())+([B][COLOR=red]12[/COLOR][/B]-B2))

This copies down to the remaining rows to indicate year of graduation.
Hope that helps,
Cindy

 

[xenou]

Cindy Ellis is my *statistics* hero

 

[kyfung]

THANKS For the fantastic responses.

 

[Cindy Ellis]

Thanks for the feedback! (I've never been anyone's hero before)<!-- / message --><!-- sig -->