Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
merging multiple named ranges for validation list
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
merging multiple named ranges for validation list
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)??? joes wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation List - Can I have multiple ranges displayed? | Excel Worksheet Functions | |||
Validation list from Named Ranges | Excel Worksheet Functions | |||
Named Ranges Not Working in Data Validation | Excel Worksheet Functions | |||
How do I use Named Ranges as Data Validation Lists? | Excel Worksheet Functions | |||
Validation protection with named ranges | Excel Worksheet Functions |