Using names to represent values

  • Thread starter Thread starter Popke17
  • Start date Start date
P

Popke17

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 stil

| 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 nam
for the past hour and I am going crazy

I want the names to show in the cells so that readility remains, th
numbers associated with particular names are not important and ar
defined elsewhere but I have a ton to manipulate mathematically adn i
would be so much simpler if excel could do it for me. Thanks
 
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,,
 
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.)
 
Back
Top