Named Range Used in Formula Error

[Ronald]

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

 

[T. Valko]

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.

 

[Dave Peterson]

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)

 

[JMay]

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.