How to apply a name to a cell range

[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

 

[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

 

[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.

 

[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?

 

[Wigi]

Reference, otherwise you wouldn't need 12 cells...

 

[CmK]

can you do that link 12 cells in one by name range wihtout &?
Just curious

 

[Wigi]

What do you mean precisely, Cmk?

 

[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

 

[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.

 

[CmK]

It doesnt work only the figure in the first cell shows in the rnage

 

[Max]

It doesnt work only the figure in the first cell shows in the range

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

.. into a horiz range of the same size -- select 12 horiz cells ..

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.

---

 

[CmK]

Cheers sorry
It works like copy and paste? does it not

 

[CmK]

Cheers it works sorry

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.

---

 

[Max]

Cheers it works sorry
no prob. glad it did.

---

 

[Stephen Francis M]

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

Stephen Francis M

 

[Max]

Glad it helped, Stephen.
Thanks for posting back.