adding name values

[AKPhil]

Excel 2003

Menu: Insert => Name => Define:

N = 20
O = 30
M = 50

Let’s say cell(s):

K3 has N displayed
K4 has O displayed
K5 has M displayed

=SUM(K3:K5) displays 0

How do I make it display the number 100? As in 20+30+50=100

 

[Niek Otten]

In K3: =N

Use the = sign to get the value of the defined name.

--
Kind regards,

Niek Otten

| Excel 2003
|
| Menu: Insert => Name => Define:
|
| N = 20
| O = 30
| M = 50
|
| Let's say cell(s):
|
| K3 has N displayed
| K4 has O displayed
| K5 has M displayed
|
| =SUM(K3:K5) displays 0
|
| How do I make it display the number 100? As in 20+30+50=100
|
|

 

[Trevor Shuttleworth]

If that's how you've defined the names you would use:

=N+O+M = 100

=INDIRECT(K3) would show 20 if K3 had N in it

=INDIRECT(K3)+INDIRECT(K4)+INDIRECT(K5) = 100

Regards

Trevor

 

[William Horton]

You have actual values "N", "O", and "M" in those cells. You don't have the
names. You would have to use the INDIRECT function to get the sum.

=SUM(INDIRECT(K3),INDIRECT(K4),INDIRECT(K5))

 

[AKPhil]

Trevor (and Niek and William),

Thanks for the come back.

I do have the letter N in cell K3, letter O in cell K4, and letter M in cell
K5.
These letters will change with time, so that is why I assigned number values
to them. The running total for each row and column will have a consistant
value.

So, I need to display N,O,M in the cells but need a number at the end
representing a total.

I tried your suggestions:

=INDIRECT(K3) gives a #REF! error
same for
=INDIRECT(K3)+INDIRECT(K4)+INDIRECT(K5)
and for
=sum(INDIRECT(K3)+INDIRECT(K4)+INDIRECT(K5))
and
=sum(INDIRECT(K3),INDIRECT(K4),INDIRECT(K5))


Any other suggestions?

 

[Niek Otten]

Why use Defined names then? Just use a lookup table and the VLOOKUP() function to connect a letter to a value.

--
Kind regards,

Niek Otten

| Trevor (and Niek and William),
|
| Thanks for the come back.
|
| I do have the letter N in cell K3, letter O in cell K4, and letter M in cell
| K5.
| These letters will change with time, so that is why I assigned number values
| to them. The running total for each row and column will have a consistant
| value.
|
| So, I need to display N,O,M in the cells but need a number at the end
| representing a total.
|
| I tried your suggestions:
|
| =INDIRECT(K3) gives a #REF! error
| same for
| =INDIRECT(K3)+INDIRECT(K4)+INDIRECT(K5)
| and for
| =sum(INDIRECT(K3)+INDIRECT(K4)+INDIRECT(K5))
| and
| =sum(INDIRECT(K3),INDIRECT(K4),INDIRECT(K5))
|
|
| Any other suggestions?
|
| "Trevor Shuttleworth" wrote:
|
| > If that's how you've defined the names you would use:
| >
| > =N+O+M = 100
| >
| > =INDIRECT(K3) would show 20 if K3 had N in it
| >
| > =INDIRECT(K3)+INDIRECT(K4)+INDIRECT(K5) = 100
| >
| > Regards
| >
| > Trevor
| >
| >
| > | > > Excel 2003
| > >
| > > Menu: Insert => Name => Define:
| > >
| > > N = 20
| > > O = 30
| > > M = 50
| > >
| > > Let's say cell(s):
| > >
| > > K3 has N displayed
| > > K4 has O displayed
| > > K5 has M displayed
| > >
| > > =SUM(K3:K5) displays 0
| > >
| > > How do I make it display the number 100? As in 20+30+50=100
| > >
| > >
| >
| >
| >

 

[AKPhil]

The spreadsheet printout will have a collection of N's, O's, and M's as well
as other letters on it. Combinations will vary, such as NNOMMM or MOOONNO
etc.

I will have a tally line across the bottom and one on the side. Horizontal
rows will equal a given number. Vertical rows will equal a different but
constant number. These numbers will indicate at a glance whether the right
combination of letters have been use.

Therefore, the values for each letter must be exact. I looked at the lookup
command as you suggested. Not sure that command handles exactly what I'm
trying to do.

In simple terms, I just want to add the values in each row and then
separately add the values in each column. However, instead of numbers being
displayed in the spreadsheet, letters are displayed.

 

[AKPhil]

Disregard what I just wrote, below.

Niek you are absolutely correct.

After going round and round with numerous functions, I got the lookup to work.

Made a Lookup table with Letters in the first column and numbers in the
second column. The syntax looks like:

=SUM(VLOOKUP(H25,B40:C46,2,FALSE)+VLOOKUP(I25,B40:C46,2,FALSE)+VLOOKUP(J25,B40:C46,2,FALSE))

The spreadsheet has 10 columns so the =sum will be a lot longer but you get
the idea.

Thanks to Trevor, William, and especially Niek; your input was very helpful,
got me running down the right road!! Thanks again!