Dynamic/Offset Data Validation

Hi all,

I've got a repitition problem. I have multiple data ranges in one worksheet where I need to create a named range for each. Example:

-------A
1----Name
2-----a
3-----b
4-----c
5-----
6-----

This continues for quite a lot of columns across the worksheet. I know that to do this once I would highlight cells A1:A6 and create a name on the formula tab, create from selection, top row. I also know that I could just highlight cells A1:ZZ6 and create from selection and it would do that too.

My other problem is that not every column will contain 6 values so will be blank which I wouldn't want appearing in Data Validation lists. And again, I know that if I was only doing this once I would use the name manager and edit the range using the offset formula. But, as you can imangine I don't want to be doing this for hundred of named ranges.

Does anybody know a way how I can combine these methods but also to do it automatically rather than having to repeat this process for every column.

Thanks in advance,

Jason

Or alternatively...

Is it possible to have data validation lists where insted of defining a name or a range of cells to use as the source, use a formula that would search a seperate worksheet for a cell containing any given unique phrase and use the 6 non blank cells below that cell as the data validation source. If that is possible, I think it would be the perfect solution for me. I've tried doing google searches around this but that only led me to dynamic named ranges, the offset formula for blanks and using table as source ranges.

Hi,

Provide sample workbook with out any confidential data .

Punnam

Originally Posted by Punnam

Hi,

Provide sample workbook with out any confidential data .

Punnam

Sample workbook attached.

Originally Posted by Olly

This code will create a named range for every column in Sheet1.

The ranges are dynamic, so as values are added / deleted, the named range will continue to reflect the list of values.

Sub dvRangesDefine()
Dim lCol As Long

With Sheet1
    For lCol = 1 To .Cells(1, Columns.Count).End(xlToLeft).Column
        ThisWorkbook.Names.Add _
            Name:="dv" & .Cells(1, lCol).Value, _
            RefersToR1C1:="=OFFSET('" & .Name & "'!R1C" & lCol & ",1,0,COUNTA('" & .Name & "'!C" & lCol & ")-1,1)"
    Next lCol
End With
End Sub

Hi Olly,

Could I insert this code to have it name all the ranges, but then delete the code and the names remain? The reason I ask is because this document is to be used on an iPad Air with the office Excel app which doesn't seem to like Macro Enabled Workbooks and just crashes.

Also, I imagine this is going to change the code entirely:

There is more than one range of data that I would need to be a named range within any one column. I presume your code would just roll all these up into one named range? Bit hard to explain but if you have a look at the sample file I've uploaded you'll see what I mean.

Many thanks,

Jason