Named Range Used in Formula Error
 

Formula

=a*b*MONTH
Results in error if Named Range is picked up from a drop down box

eg: Jan set of values
Feb set of values

Putting named range in formula directly works fine.
Point to a cell called month where users select the appropriate month
yields errors

How do I get the range values passed correctly to the formula if the
drop down box is named MONTH?
Where Month = Drop Down
and Month is a named range

Workarounds??

Named Range Used in Formula Error
 

Let's see if I have this straight...

A cell is named MONTH and contains a drop down that contains the names of
months which are in turn named ranges themselves?

INDIRECT(MONTH)

Note that this won't work if the named ranges are dynamic ranges.

--
Biff
Microsoft Excel MVP


"Ronald" wrote in message
...
Formula

=a*b*MONTH
Results in error if Named Range is picked up from a drop down box

eg: Jan set of values
Feb set of values

Putting named range in formula directly works fine.
Point to a cell called month where users select the appropriate month
yields errors

How do I get the range values passed correctly to the formula if the
drop down box is named MONTH?
Where Month = Drop Down
and Month is a named range

Workarounds??

Named Range Used in Formula Error
 

First, I wouldn't use a named range of Month. It looks too much like the
=month() worksheet function built into excel.

Maybe you want...

=a1*b1*indirect(myMonth)




Ronald wrote:

Formula

=a*b*MONTH
Results in error if Named Range is picked up from a drop down box

eg: Jan set of values
Feb set of values

Putting named range in formula directly works fine.
Point to a cell called month where users select the appropriate month
yields errors

How do I get the range values passed correctly to the formula if the
drop down box is named MONTH?
Where Month = Drop Down
and Month is a named range

Workarounds??

--

Dave Peterson

Named Range Used in Formula Error
 

In Code you can refer to an Item within your named range, like
from the immediate window:

? Range("Month").Item(6)

produces June where June is the 6th item in the named range Month.

"Ronald" wrote:

Formula

=a*b*MONTH
Results in error if Named Range is picked up from a drop down box

eg: Jan set of values
Feb set of values

Putting named range in formula directly works fine.
Point to a cell called month where users select the appropriate month
yields errors

How do I get the range values passed correctly to the formula if the
drop down box is named MONTH?
Where Month = Drop Down
and Month is a named range

Workarounds??