Using names to represent values

I am trying to get names to represent values, e.g.
Bill=3
bob=4
Stan=22
so that after I type a bunch of names into the cells, the view is still

| Bill | Bob | Stan |
but that I can total them so it would look like this
| Bill | Bob | Stan | 29 |

is this possible and how? I have been moneying with insert define name
for the past hour and I am going crazy

I want the names to show in the cells so that readility remains, the
numbers associated with particular names are not important and are
defined elsewhere but I have a ton to manipulate mathematically adn it
would be so much simpler if excel could do it for me. Thanks.


--
Popke17
------------------------------------------------------------------------
Popke17's Profile: http://www.excelforum.com/member.php...o&userid=29403
View this thread: http://www.excelforum.com/showthread...hreadid=491150

One way
Set up a table of the names and their values.

use a vlookup formula to select the number depending on the name entered.
put these vlookup formula's in hidden columns to the right of your sum
answer
sum the vlookup cells

example
=VLOOKUP(C1,$R$1:$S3,2,0)

where C1 is the cell with a name
$R$1:$S$3 is the table
2 indicates to lookup value in the second column of the table.

so you'll get

A1 B1 C1 D1 E1
F1
bill bob sum(E1:G1) =VLOOKUP(A1,$R$1:$S3,2,0)
=VLOOKUP(B1,$R$1:$S3,2,0) etc,,

--
Greetings from New Zealand
Bill K

"Popke17" wrote in
message ...

I am trying to get names to represent values, e.g.
Bill=3
bob=4
Stan=22
so that after I type a bunch of names into the cells, the view is still

| Bill | Bob | Stan |
but that I can total them so it would look like this
| Bill | Bob | Stan | 29 |

is this possible and how? I have been moneying with insert define name
for the past hour and I am going crazy

I want the names to show in the cells so that readility remains, the
numbers associated with particular names are not important and are
defined elsewhere but I have a ton to manipulate mathematically adn it
would be so much simpler if excel could do it for me. Thanks.


--
Popke17
------------------------------------------------------------------------
Popke17's Profile:
http://www.excelforum.com/member.php...o&userid=29403
View this thread: http://www.excelforum.com/showthread...hreadid=491150

Another way, not sure if this is really usable but ...
First I copied your data like so:
Bill 3
bob 4
Stan 22
Then I did a Insert/Name/Create and answered left column (which it had
already filled in for me as I had selected the above range prior to issuing
the command. This created the name ranges such that Bill referred to the
cell in which the number 3 resides. Then I tried the following formula which
is probably not what you want:
=+Bill+bob+Stan
and it worked, but then I tried the following formula which may be what you
want, but might become unwieldy if you have too many names that you are
trying to add:
=INDIRECT(E12)+INDIRECT(E13)+INDIRECT(E14)
Of course e12 through e14 are where the 3 names reside (bill, bob, and stan.)



"Bill Kuunders" wrote:

One way
Set up a table of the names and their values.

use a vlookup formula to select the number depending on the name entered.
put these vlookup formula's in hidden columns to the right of your sum
answer
sum the vlookup cells

example
=VLOOKUP(C1,$R$1:$S3,2,0)

where C1 is the cell with a name
$R$1:$S$3 is the table
2 indicates to lookup value in the second column of the table.

so you'll get

A1 B1 C1 D1 E1
F1
bill bob sum(E1:G1) =VLOOKUP(A1,$R$1:$S3,2,0)
=VLOOKUP(B1,$R$1:$S3,2,0) etc,,

--
Greetings from New Zealand
Bill K

"Popke17" wrote in
message ...

I am trying to get names to represent values, e.g.
Bill=3
bob=4
Stan=22
so that after I type a bunch of names into the cells, the view is still

| Bill | Bob | Stan |
but that I can total them so it would look like this
| Bill | Bob | Stan | 29 |

is this possible and how? I have been moneying with insert define name
for the past hour and I am going crazy

I want the names to show in the cells so that readility remains, the
numbers associated with particular names are not important and are
defined elsewhere but I have a ton to manipulate mathematically adn it
would be so much simpler if excel could do it for me. Thanks.


--
Popke17
------------------------------------------------------------------------
Popke17's Profile:
http://www.excelforum.com/member.php...o&userid=29403
View this thread: http://www.excelforum.com/showthread...hreadid=491150