Dynamic Validation Range

izzy said:

I'm using a Validation list with A48:A20000 as my range. Data is only present in maybe 10,000 rows but grows constantly. Is there a way I can make my range dynamic? Such as using an xlUP OnWorkBookOpen() and grabbing that address somehow?

Click to expand...

Why no define a name by means of a dynamic formula? If interested, please state what kind of data you have from A48 on and the name of the sheet of interest.

Hey great idea. I know i've seen dynamic range naming in the archives somewheres. Thanks Aladin.

A:A contains part numbers with leading zeros so it's all formated as text. No blanks.
Sheet1 (DataBase)

izzy said:

Hey great idea. I know i've seen dynamic range naming in the archives somewheres. Thanks Aladin.

A:A contains part numbers with leading zeros so it's all formated as text. No blanks.
Sheet1 (DataBase)

Click to expand...

Activate Insert|Name|Define.
Enter BigStr as name in the Names in Workbook box.
Enter the following in the Refers to box:

=REPT("z",255)

Click Add.

Enter DB as name in the Names in Workbook box.
Enter the following in the Refers to box:

=Sheet1!$A$48:INDEX(Sheet1!$A:$A,MATCH(BigStr,Sheet1!$A:$A))

Click OK.

Now you can use DB as source in a cell that you want to data validate and in formulas.

Like a charm