[SOLVED] Data Validation List/Named Range issue

[jellybean1974]

Hi there

I'm new to the fourm, so did a bit of searching to see if I could find similar problem with solution, but couldn't. So here it goes

For a bit of context, I work in the IT dept for a market research company. I have a template workbook with multiple tabs for each of our Analysts to use for their forecast figures. For example

Tab1=Quarterly Forecast of Mobile Handset Shipments by Region/Operating System
Tab2=List of Operating Systems (as Named Range OS)
Tab3=List of Regions (as Named Range All_Regions)
Tab4=List of Measures (as Named Range All_Measures)

What I'm trying to achieve is for all our Analysts to use standardized names for Operating systems, Regions and Measures.

I thought I could do this with Data Validation Lists where the Source =NamedRange, and on the 1st cell it tried it on with the "All_Measures" Named Range it did work. However, when I used the same Data Validation on another cell (still with Source as "All_Measures" Named Range), the bottom 4 measures from the list did not appear?

When I open the named range with the 1st cell I tried it on highlighted, I get a different range of cells in the Refers to box, than when I open the named range with the other cell I tried it on highlighted?

There is only one Named Range called All_Measures, how can the "Refers to" be different?

 

[NARAYANK991]

Hi ,

Can you post the formula in the Refers To box for the named range All_Measures ?

Narayan

 

[jellybean1974]

=Measures!$A$9:$A47

 

[NARAYANK991]

Hi ,

Shouldn't it be =Measures!$A$9:$A$47 ?

Narayan

 

[Somendra Misra]

@jellybean1974

I think the relative row number 47 is creating the problem.

Try below formula:
=Measures!$A$9:$A$47

Regards,

 

[jellybean1974]

Thanks @NARAYANK991 and @Somendra Misra

Absolutely, yes. But what happens when there is a new measure added to the list?

J

 

[Somendra Misra]

Create a dynamic named range as below:

=Measures!$A$9:INDEX(Measures!$A$9:$A$500,MATCH("zzzzz",Measures!$A$9:$A$500))

if the measures are text, try below for numbers:

=Measures!$A$9:INDEX(Measures!$A$9:$A$500,MATCH(9.999999E+307,Measures!$A$9:$A$500))

This will expand the list till 500 rows.

Regards,

 

[jellybean1974]

Thanks @Somendra Misra

That's brilliant. Working perfectly now.

J