Validate List from non-Adjacent cells

[ElmerS]

Hi,
My question is explained inside the attached WB.
The items of the list, in cell C8, should be adjacent (no gaps)
Thanks, Elm

[DonkeyOte]

ElmerS, the attachments in your posts should support the narrative in your post and not vice versa - please ask your question within the post rather than the supporting document(s).

Using helpers is the most sensible approach here regardless of preference.

For ex.

Please Login or Register  to view this content.

Then use a Dynamic Named Range

Please Login or Register  to view this content.

Then set Validation List source to be = _dvList

Thus as you alter content of A2:A14 so your Data Validation list will alter accordingly.

[ElmerS]

In that case I prefer the VBA approach:

Please Login or Register  to view this content.

Thanks anyway,

[DonkeyOte]

No problem though from your prior posts I always assumed you had a complete aversion to VBA given the insistence on formulae resolution (even in the face of inefficiency).

If using VBA it would probably make sense to apply it to a Change event against A2:Ax given alterations on those cells should in turn affect the validation list itself... however ignoring that for the being...

-- using [C8] is generally slower than using Range("C8")

-- per your pseudo-code Test should read CL

-- if as assumed above A2:Ax are constants you can further limit the iteration by using SpecialCells - ie ignore blanks in the first instance

Please Login or Register  to view this content.

[ElmerS]

Thanks,

No - no "aversion" against VBA and/or Helper columns but I prefer not to use them UNLESS there is no other solution.

As I rely on your judgment & Excel knowledge and after reading your first reply, to this question, I wrote this short piece of code.

Elm