merging multiple named ranges for validation list

[joes]

Hello

I have 2 named ranges and I like to have a validation in a cell (list
validation) wich refers to both ranges. The named ranges are on
different pages (sheets). I like not to use a separate 3rd column as
temporary source (which has alle entries from the other two names
ranges). Instead I like to merge named ranges ad hoc with some
vbscript. Exists there a way?

i.e. the following is just for illustration

Validation Function "As List"
=mergeRanges(rangeA;rangeB)

function mergeRanges(r1 as Range, r2 as Range) As Range

Dim rTarget = new Range

' merge the two ranges r1,r2 to rTarget

mergeRanges = rTarget

end Function

 

[Dave Peterson]

I don't think so. You can't use multiple areas manually either.

But you could build that range (in code) on a helper worksheet, then use that in
your data|validation.

If the range changes sizes, maybe rebuild it each time the workbook opens (or
when the ranges change)???