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

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

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!


--
chiliman
------------------------------------------------------------------------
chiliman's Profile: http://www.excelforum.com/member.php...o&userid=34567
View this thread: http://www.excelforum.com/showthread...hreadid=543378