Define Name over several cells

[Davidwazza]

Hi all,

I seem to have this problem, hopefully it is easy to solve.

I want to define a list that I want to use as a dropdown list in another sheet, so need to define a name over two entries in cell E10 and E18. I select both those cells, and Define -> Name, but it doesn't seem to work:
='Sheet 1'!$E$10, 'Sheet 1'!$E$18

I have tried to redefine as an array, e.g.
={'Sheet 1'!$E$10, 'Sheet 1'!$E$18}
but this doesn't seem to work.

Anyone have any ideas?

 

[dine]

Hi,

I hope the validation list will work only for continuous named ranges. But I hope the below code might help. Alter the code as required.

Sub test()
    Sheets(2).Range("B5").Validation.Add Type:=xlValidateList, Formula1:=Sheets(1).Range("E10").Value & ", " & Sheets(1).Range("E18").Value
End Sub

 

[Davidwazza]

Ok interesting, thanks dine.

Obviously this will only update if I run that function, so what would I need to add to update it, e.g. I guess if E10/E18 gets updated, it will automatically update that data validation function? Possibly run test() during startup of the excel sheet too?

 

[dine]

Post the below code in ThisWorkbook in VBA.
Now the code will execute once you open the workbook.

Private Sub Workbook_Open()
    Sheets(2).Range("B5").Validation.Add Type:=xlValidateList, Formula1:=Sheets(1).Range("E10").Value & ", " & Sheets(1).Range("E18").Value
End Sub

Hope this helps!!

Thanks,
Dine