ExcelBanter - adding name values

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - adding name values (https://www.excelbanter.com/excel-worksheet-functions/85487-adding-name-values.html)

adding name values

Excel 2003

Menu: Insert = Name = Define:

N = 20
O = 30
M = 50

Lets 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

adding name values

adding name values

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

"AKPhil" wrote in message
...
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

adding name values

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" wrote:

Excel 2003

Menu: Insert = Name = Define:

N = 20
O = 30
M = 50

Lets 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

adding name values

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

"AKPhil" wrote in message
...
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

adding name values

adding name values

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.

"Niek Otten" wrote:

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

"AKPhil" wrote in message ...
| 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
|
|
| "AKPhil" wrote in message
| ...
| 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
|
|
|
|
|

adding name values

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!

"AKPhil" wrote:

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.

"Niek Otten" wrote:

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

"AKPhil" wrote in message ...
| 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
|
|
| "AKPhil" wrote in message
| ...
| 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
|
|
|
|
|