Using Conditional Data Validation with Dynamic Name Ranges

[Samsung]

Hi,

I've got a conditional validation rule setup where the user is filling out details for an employee.

First they select a department, then they select a role from that department.

On another sheet is a row with departments, with all the roles for that department listed below. I've setup a dynamic name range on those roles so that additional roles can be added or removed from each department and that bit works fine, however the =INDIRECT function (used in the conditional data validation) does not seem to work well with it.

Does anyone have any solutions?

Thanks in advance,
Samsung

 

[jbeaucaire]

The problem isn't that INDIRECT() Data Validation and Dynamic named ranges don't work well, they don't work at all. You'll have to give up on the dynamic named ranges to keep the more useful dynamic Data validation.

Manually se the named ranges.

 

[T. Valko]

Hi,

I've got a conditional validation rule setup where the user is filling out details for an employee.

First they select a department, then they select a role from that department.

On another sheet is a row with departments, with all the roles for that department listed below. I've setup a dynamic name range on those roles so that additional roles can be added or removed from each department and that bit works fine, however the =INDIRECT function (used in the conditional data validation) does not seem to work well with it.

Does anyone have any solutions?

Thanks in advance,
Samsung

What does your formula for the dynamic range look like?

If the dynamic range is defined using functions like OFFSET then INDIRECT won't work.

What are you using INDIRECT for?

 

[Samsung]

Hi guys, thanks for your help. Yes the dynamic name range involves offsetting. It's so extra roles can be added to departments. I think I will have to either manually update the ranges, or I could create a macro to delete all named ranges and then define them again.

Its a shame Dynamic Ranges doesn't work with conditional data validation!

Thanks again

Sam

 

[T. Valko]

Hi guys, thanks for your help. Yes the dynamic name range involves offsetting. It's so extra roles can be added to departments. I think I will have to either manually update the ranges, or I could create a macro to delete all named ranges and then define them again.

Its a shame Dynamic Ranges doesn't work with conditional data validation!

Thanks again

Sam

OK, thanks for feeding back!

 

[Aladin Akyurek]

Hi guys, thanks for your help. Yes the dynamic name range involves offsetting. It's so extra roles can be added to departments. I think I will have to either manually update the ranges, or I could create a macro to delete all named ranges and then define them again.

Its a shame Dynamic Ranges doesn't work with conditional data validation!

Thanks again

Sam

If the number of sublists are not to many, you can implement something like:

=CHOOSE(MATCH(E2,List,0),Kad,Jad,Lad)

where Kad, Jad, and Lad are items in List and are dynamic names ranges. E2 is a data-validated cell with List as Source.

 

[jbeaucaire]

Actually, if employing VBA to adapt your dynamic ranges for you into specified ranges, I would opt for a sheet called LISTS that holds all these ranges, row 1 would hold the "name" for each range of values...the values would be listed in row2 downward for each column.

Excel Workbook
	A	B	C
1	Cat	Dog	Bird
2	Chester	Fido	Polly
3	Simon	Spot	*******
4	Morris
Sheet1

Then I'd use a WS_CHANGE event built into that sheet that recreates the named ranges for you anytime you make a change on that sheet, as you go.

This macro in the sheet module itself would do that:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, rName As String, Rws As Long, Col As Long

For Each cell In Target
    rName = Cells(1, Target.Column)
    Col = Target.Column
    Rws = Cells(Rows.Count, Col).End(xlUp).Row
    
    On Error Resume Next
    ActiveWorkbook.Names(rName).Delete
    ActiveWorkbook.Names.Add Name:=rName, _
        RefersToR1C1:="='" & Me.Name & "'!R2C" & Col & ":R" & Rws & "C" & Col

Next cell

End Sub