How Do I assign a numeric value to a text entry?
 

Okay.. It's been a while since I've had my Excel courses. Here's th
scenario...

I have a worksheet that is tracking 'points' for a real estate sale
contest. Certain transactions performed with a property are 'worth
more points than others. Here's an example of what the columns loo
like:
------------ --------- -------------- ----------- -------------
-------------------- --------------
Address |-Sale-- |-Points: 1--|--Listing--|-Points: 2--|-In Hous
Sale--|--Points:3--
----------- -------- -------------- ----------- -------------
-------------------- --------------
123 A St.| Joe S. |------1------ |-Jane S.--|------2------|---Jil
S.------------|-----3-------
------------ -------- -------------- ----------- -------------
-------------------- --------------
456 B St.|---------|--------------| Sally T. |------2 ------|--Dav
R.---------|-----3--------
------------- --------- --------------- ------------ --------------
--------------------- --------------
My question is: How do I formulate the 'Points' cells to automaticall
populate with the correct number (1, 2, 3 points) just by typing tex
(the salesperson's name) into the column beside it? ALSO, how do
calculate all the points each salesperson has earned on anothe
worksheet if their name appears in different columns and row
throughout the worksheet (without doing =SUM ([and CTRL+clicking al
the different cells]) )??

Please Help!
-KL

--
KathyDent197
-----------------------------------------------------------------------
KathyDent1976's Profile: http://www.excelforum.com/member.php...fo&userid=1501
View this thread: http://www.excelforum.com/showthread.php?threadid=26631

How Do I assign a numeric value to a text entry?
 

in the cells under the points column

=If(cell to left = "","",1)

replace the 1 with 2 or 3 for the other columns.

so if cell C2

=if(B2="","",1)

assuming points are in C, E, G and names in B, D, F all on Sheet1.

=sumif(sheet1!B:B,"Joe",Sheet1!C:C) + sumif(sheet1!D:D,"Joe",Sheet1!E:E) +
sumif(sheet1!F:F,"Joe",Sheet1!G:G)

you can replace "Joe" with a cell reference on that sheet that contains Joe.
then you can put this formula next to a list of names and drag fill down the
column.

=sumif(sheet1!B:B,B2,Sheet1!C:C) + sumif(sheet1!D:D,B2,Sheet1!E:E) +
sumif(sheet1!F:F,B2,Sheet1!G:G)

for example with B2 on Sheet2 containing Joe.

--
Regards,
Tom Ogilvy


"KathyDent1976" wrote in
message ...

Okay.. It's been a while since I've had my Excel courses. Here's the
scenario...

I have a worksheet that is tracking 'points' for a real estate sales
contest. Certain transactions performed with a property are 'worth'
more points than others. Here's an example of what the columns look
like:
------------ --------- -------------- ----------- --------------
-------------------- --------------
Address |-Sale-- |-Points: 1--|--Listing--|-Points: 2--|-In House
Sale--|--Points:3--
----------- -------- -------------- ----------- --------------
-------------------- --------------
123 A St.| Joe S. |------1------ |-Jane S.--|------2------|---Jill
S.------------|-----3-------
------------ -------- -------------- ----------- --------------
-------------------- --------------
456 B St.|---------|--------------| Sally T. |------2 ------|--Dave
R.---------|-----3--------
------------- --------- --------------- ------------ ---------------
--------------------- --------------
My question is: How do I formulate the 'Points' cells to automatically
populate with the correct number (1, 2, 3 points) just by typing text
(the salesperson's name) into the column beside it? ALSO, how do I
calculate all the points each salesperson has earned on another
worksheet if their name appears in different columns and rows
throughout the worksheet (without doing =SUM ([and CTRL+clicking all
the different cells]) )??

Please Help!
-KLD


--
KathyDent1976
------------------------------------------------------------------------
KathyDent1976's Profile:
http://www.excelforum.com/member.php...o&userid=15013
View this thread: http://www.excelforum.com/showthread...hreadid=266312