How to apply a name to a cell range

  • Thread starter Stephen Francis M
  • Start date
S

Stephen Francis M

I have created a name range for monthly sales (Jan thro to Dec) for each
product. Example Name Sales_Prod1_06 which applies to cells N12:y12. On a
summary worksheet I now want to reference this range and enter
=Sales_Prod1_06. However I get Value! error if the name range is not entered
in column N:y on the summary worksheet.

I am sure I was able to do this without any probelm in the old version of
excel ( I am now using 2007)

Thanks for your help
 
N

Niek Otten

Maybe you mean

=SUM(Sales_Prod1_06)

?
If not, which of the cells from the named range do you expect the formula to return?

If you refer to the type of "names" used in "Accept labels in formulas"; that feature is no longer present in Excel 2007

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I have created a name range for monthly sales (Jan thro to Dec) for each
| product. Example Name Sales_Prod1_06 which applies to cells N12:y12. On a
| summary worksheet I now want to reference this range and enter
| =Sales_Prod1_06. However I get Value! error if the name range is not entered
| in column N:y on the summary worksheet.
|
| I am sure I was able to do this without any probelm in the old version of
| excel ( I am now using 2007)
|
| Thanks for your help
 
W

Wigi

No, you were not, since I can't do it here with my 2003 version.

Or you did something else than you write... ;-)

Alternatively, you could use the INDEX function and retrieve the 1st till
12th element of the named range.
 
C

CmK

So what did you want to do with the array ?
Did you want to sum it or just reference the whole array in one cell?
 
C

CmK

Hi Wigi

I didnt get what you meant by reference in your answer to my original question
"Reference, otherwise you wouldn't need 12 cells..."

If i name a range(a1:a99) as cmk
and reference it in cell B1 =cmk
whats that going to achieve unless he was trying to sum it like =sum(cmk)
This is refering to the original question of htis message group
"=Sales_Prod1_06. However I get Value! error if the name range is not entered
in column N:y on the summary worksheet. "

I hope i explained my self

Thanks
 
M

Max

=Sales_Prod1_06
Think you must array-enter the above multi-cell defined range (ie press
CTRL+SHIFT+ENTER - CSE) into a horiz range of the same size -- select 12
horiz cells, place the formula into the leftmost active cell, then press CSE.
This will fill the selected range with the contents of the defined range.
 
M

Max

CmK said:
It doesnt work only the figure in the first cell shows in the range
Click to expand...

Thought my response mentioned that it should be array-entered:
.. into a horiz range of the same size -- select 12 horiz cells ..
Click to expand...

If you array-enter the defined range into a single cell, then it shows only
the 1st element. Array-enter into 2 horiz cells, it'll show the first 2
elements, and so on. You can try this and see for yourself.
 
C

CmK

Cheers it works sorry

Max said:
Thought my response mentioned that it should be array-entered:

If you array-enter the defined range into a single cell, then it shows only
the 1st element. Array-enter into 2 horiz cells, it'll show the first 2
elements, and so on. You can try this and see for yourself.
Click to expand...
 
S

Stephen Francis M

Hi Max
Your solution achieved exactly what I wanted. Many thanks and Happy New Year

Stephen Francis M
 
Top