I have a worksheet ("Lists") that contains lists of various groups of items I
use in the workbook. I want to create a 'Name' the contains non-adjacent
cells; i.e., A1:A20, H12:H45, U27:U44. If I use the 'Define Name' option on
the Formula tab, it includes all three ranges in the name definition, but
then if I use the Name for validation in a cell, the dropdown only shows the
first range. How do I get it to show all cell values in the dropdown?

Assuming the above can be fixed, I would also like (in some instances) to
use a variable for the ending cell range, such as CONCATENATE("A1:A",$B$1).
I have tried this combined with INDIRECT, and it is accepted as a valid
formula, but when I use the Name for validation, when I click on the
dropdown, nothing happens (which generally signifies there is an error with
the Name used for validation). Any suggestions on this?

I am using Excel 2007 (which is probably a major part of the problem).

--
Bill @ UAMS