Dynamic named range for use in data validation list

[JackDanIce]

Hi,

I have a sheet Settings, A1 contains value "Check_Values", A2:A? are values to be allowed in a drop-down data validation list, where ? is unknown row number.


For a dynamic range, I have named A1 ("Check_Values") and then use formula to name range ("Check_Values_List") for dynamic list: =OFFSET(Check_Values,1,0,COUNTA(Settings!$A:$A)-1,1)


I'd like argument in COUNTA to not refer to sheet name and column address; instead refer to "The column named range Check_Values is in" so there are no direct references to sheet name and column header in the formula.


I.e. =OFFSET(Check_Values,1,0,COUNTA(column Check_Values is in)-1,1)


Can someone suggest correction? Thanks in advance,
Jack

 

[Marcelo Branco]

Maybe...

=OFFSET(Check_values,1,,COUNTA(OFFSET(Check_values,,,1000))-1)

Note that 1000 is arbitrary - you can change it.

M.

 

[JackDanIce]

Awesome @Marceloa Branco list size will unlikely ever exceed 50, should have worked that out myself but much appreciated, thank you

 

[Marcelo Branco]

You are welcome. Glad to help.

M.

 

[JackDanIce]

Hi Marcelo, sorry if this should be a new thread, I'm curious about a follow-up.

Have named A1 as Sheet_Check, then defined a name, Sheet_Check_Range as =OFFSET(Sheet_Check,1,0,COUNTA(OFFSET(Sheet_Check,1,0,500,1)),5), which currently is range A1:E48 (scope workbook)

This code errors, with method range global failed:

Sub b()
Dim r As Range
Set r = Range("Sheets_Check_Range")
End Sub

Is it because it can't evaluate a dynamic range from the sheet into a VBA object?

Was trying something different from the usual Last Row/Last Column (always 5 in this case) approach to define an unknown row-size range.

 

[Marcelo Branco]

Have named A1 as Sheet_Check, then defined a name, Sheet_Check_Range as

It seems you're using wrong name

Set r = Range("Sheets_Check_Range")

Shouldn't it be?
Set r = Range("Sheet_Check_Range")

M.

 

[JackDanIce]

D'oh, school boy error! Thanks, have working, used named A1: Sht, dynamic area: Sht_Rng and Range("Sht_Rng").Select works, thanks again