adding more than one value to cells

[chiliman]

Hello all, your help would be most appreciated. I have a peculiar situation. I have a list of cell values (in my case it is Job codes). I want to assign each job code a numerical value. For example: I want to assign the first job code the number one, the second number two, and so on.

From there I will have a row in which a user has put an employee's name and their assigned job codes for a particular week. At the end of that row I need a cell that looks at the cells to the right, evalutates which job codes are in those cells, figures out which numbers are associated with those job codes, adds those numbers together, and displays the final sum in that cell.

I am not even sure where to begin with this, so your help would be greatly appreciated. I would like to do this with excel's inherent functions, if possible.

For those interested, here is the purpose. I have made an employee schedule for my employer. Now my manager wants to take the job codes and assign values to them which will represent a length of time. (For example, the dinner bar shift should be about 8 hours, the dining room section A shift should only be about 6 hours, and the evening to-go shift should be only 5 hours). He wants the schedule to look at all the job codes he has put for each particular employee for that week and tell him how many projected hours that he or she will be working that week.

Thank you for your help!

[Barb Reinhardt]

Do you have a cross-reference for the job code to the numerical reference?
If so, all you should need to do is a vlookup.

Let's say you have your job code in A1
Your xref is on Sheet2 in A1:B20 (where the job code is in column A and the
numerical ref is in column B)
In column B1 of the first sheet put this
=vlookup(A1,'Sheet2'!A1:B2,2,false)

Note that the values in sheet 2 need to be sorted in ascending order.


"chiliman" wrote:

>
> Hello all, your help would be most appreciated. I have a peculiar
> situation. I have a list of cell values (in my case it is Job codes). I
> want to assign each job code a numerical value. For example: I want to
> assign the first job code the number one, the second number two, and so
> on.
>
> From there I will have a row in which a user has put an employee's name
> and their assigned job codes for a particular week. At the end of that
> row I need a cell that looks at the cells to the right, evalutates
> which job codes are in those cells, figures out which numbers are
> associated with those job codes, adds those numbers together, and
> displays the final sum in that cell.
>
> I am not even sure where to begin with this, so your help would be
> greatly appreciated. I would like to do this with excel's inherent
> functions, if possible.
>
> For those interested, here is the purpose. I have made an employee
> schedule for my employer. Now my manager wants to take the job codes
> and assign values to them which will represent a length of time. (For
> example, the dinner bar shift should be about 8 hours, the dining room
> section A shift should only be about 6 hours, and the evening to-go
> shift should be only 5 hours). He wants the schedule to look at all the
> job codes he has put for each particular employee for that week and tell
> him how many projected hours that he or she will be working that week.
>
>
> Thank you for your help!
>
>
> --
> chiliman
> ------------------------------------------------------------------------
> chiliman's Profile: http://www.excelforum.com/member.php...o&userid=34567
> View this thread: http://www.excelforum.com/showthread...hreadid=543378
>
>

[chiliman]

I understand what you are saying, but only partially. I do not see how that would work, though I may be missing it.

What I need the formula to do is look at the cells preceeding it in the row, and based on a list of job codes determine what job codes are listed in that row, then determine what value is assigned to them, and add those values.

I believe that I have roughly worked out the logic to do this which I will attempt to write out in english here.

#1: I want the cell (let's say cell A4) to "think": "Is there a job code in cell A1 that is found in the job code list? Yes, it is "Bar". Ok then, what is the number associated with that job code? It is 8. Store the number 8. Now, is there a job code in cell A2 that is found in the list? No. Is there a job code in cell A3 that is found in the list? Yes, it is "Section A." What is the number associated with that job code? It is 6. Store the number 6. Any more cells? No. Now what is the sum of all the numbers that I have found. It is 14. I display the number 14. "

So basically this is the "does the value in Cell "whatever" match a value in a certain list, and if so, what is the number associated with that value" method.

This may not be the only logic to determine this information, but basically the formula needs to evaluate each cell that preceeds it, determine if the value in each cell matches a value in a list, and then assign a number to those values that match, and add the numbers then display the sum.

I understand that I will have to make two lists: the first is a job code list, and directly next to it will be a list of numbers associated with each code, so that if in cell A20 I have "bar," then in cell B20 I have "8." And if in cell A21 I have "Section A," then in cell B21 I have "6." And so on.

Thanks again!