Need to assign value to a cell, based on pull down menu option chosen

Hello everyone!
I’m new here and a basic excel user. I’m doing a project that’s out of my league and need some help please.
We are changing our attendance policy to a rolling point system. Points expire after one year and there are different point values for different offenses.
Currently there are 21 types of offenses.

I have managed to make a pull-down menu in one column for “offense date” which I pull the data from the “lists” tab and another pull down menu on a different column for the “offense”.

What I CANNOT figure out how to do is, make a value auto populate the cell after an offense is chosen.

For example: If I choose “NCNS” from the pull-down menu. I want 4.00 to automatically pop up on the cell next to the date.

I don’t want the managers to be able to “type” any info on the sheet, I want them to just pull down the date of the offense and the type of offense.

After I get this issued solved, I will attempt to create a formula to add the points and subtract expired points and display current balance.

I’m sorry for the long post, I’m having a hard time explaining what I want to do.

Using excel 2010 at work and 2016 from home..

Originally Posted by JeteMc

Hello vcastro458 and Welcome to Excel Forum.
Paste the following formula into B5 and drag down:

Formula:

Please Login or Register  to view this content.

While the range is selected change the format to Number with 2 decimal places (default).
Let us know if you have any questions.

Hello JeteMC,
Thank you for trying to help with my problem.
Your formula did return a result, but the values that show, do not correspond with the option chosen.
I'm reattaching my sheet again with your formula.

JeteMC you ROCK!!!!!
Thank you so much....

Now I'm going to try to figure out the formula for it to add the points and subtract the expired points automatically.... wish me luck!!

Thanks again for all your help.

I would love it, if you don't mind.
I've been working on it for about two hours and can't seem to figure it out.
I need it to add points and subtract any that are over one year old, in essence display a true total of current points only.

Something is hinky... once it hits the new year, it doesn't calculate correctly.

I attached the sheet

01/01/17 0.50 0.50
01/02/17 0.75 1.25
01/03/17 1.50 2.75
01/04/17 2.50 5.25
01/05/17 3.00 8.25
01/01/18 0.00 8.25...> this should subtract the .5 points from 1/1/17, which should then display 7.75
Does that make sense? Points expire after one year, so I need to subtract that from the running balance.

You are so great!!!!!!!

Thank you so much for all of your help. You've made this process so much easier today. I don't know if I would have ever figured that out on my own.

I'm going to put it to rest now and go to bed. I'll play with it some more tomorrow and hopefully I won't be bugging you anymore for the next phases, there's a lot more to this project that I still need to figure out.

Hope you enjoy the rest of your weekend and if you are a father or like a father to someone, Happy Father's Day to you!!