# Using names to represent values

1. ## 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 monkeying 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.

2. ## Re: Using names to represent values

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
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" <Popke17.1zmtdc_1133895005.8877@excelforum-nospam.com> wrote in
message news:Popke17.1zmtdc_1133895005.8877@excelforum-nospam.com...
>
> 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
>

3. ## Re: Using names to represent values

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
=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
> 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" <Popke17.1zmtdc_1133895005.8877@excelforum-nospam.com> wrote in
> message news:Popke17.1zmtdc_1133895005.8877@excelforum-nospam.com...
> >
> > 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
> >

>
>
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1