range name does not appear name box?

[michael.beckinsale]

Hi All,

The code snippet below is how l have added a named range to my
workbook. The variables are defined via a form and the range is added
to the workbook. The problem is that when l look for the name via the
name dropdown arrow it is not there! If l look via Insert, Names,
Define l can see it and l can do calculations, lookups etc. Can
anybody throw any light on this please, l need the name to appear in
the dropdown.

ActiveWorkbook.Names.add Name:=RngName, RefersTo:= _
"=OFFSET(" & sht & tlc & ",0,0," & Rfun & "," & Cfun & ")"

Regards

Michael

 

[Luke M]

Dynamic ranges (ones that use formulas) are volatile, and can not appear in
the drop down box. You need to somehow make it static (constant) if you want
it in the dropdown. Note that you can still use it in any formula/reference
by just typing the name.

Why do you need the name to appear in the dropdown?

 

[Luke M]

Dynamic ranges (ones that use formulas) are volatile, and can not appear in
the drop down box. You need to somehow make it static (constant) if you want
it in the dropdown. Note that you can still use it in any formula/reference
by just typing the name.

Why do you need the name to appear in the dropdown?