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

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


"Stephen Francis M" <Stephen Francis wrote in message
...
|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

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.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Stephen Francis M" wrote:

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

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?


"Stephen Francis M" wrote:

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

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

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"CmK" wrote:

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?


"Stephen Francis M" wrote:

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

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

"Wigi" wrote:

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

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"CmK" wrote:

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?


"Stephen Francis M" wrote:

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

What do you mean precisely, Cmk?

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"CmK" wrote:

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

"Wigi" wrote:

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

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"CmK" wrote:

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?


"Stephen Francis M" wrote:

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

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

What do you mean precisely, Cmk?

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"CmK" wrote:

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

"Wigi" wrote:

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

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"CmK" wrote:

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?


"Stephen Francis M" wrote:

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

=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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Stephen Francis M" wrote:
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

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



"Max" wrote:

=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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Stephen Francis M" wrote:
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

"CmK" wrote:
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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

"Max" wrote:

"CmK" wrote:
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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Cheers it works sorry

"Max" wrote:

"CmK" wrote:
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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Cheers it works sorry
no prob. glad it did.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

Stephen Francis M

"Max" wrote:

=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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Stephen Francis M" wrote:
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

Glad it helped, Stephen.
Thanks for posting back.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Stephen Francis M" wrote in
message ...
Hi Max
Your solution achieved exactly what I wanted. Many thanks and Happy New
Year

Stephen Francis M