Dropdown box with two items

Hi, I have a project that needs a dropdown box
on the data sheet E1 I like the data from B1 and B16 only E1
with this i get all $data.$B$1:$B$16
Thanks Beau


If I understand the question correctly, you can use the following formula as a source of validity for E1:

  • ~ operator for union
  • INDEX, use of the 4th argument of the function ( the index of the subrange if referring to a multiple range)
  • {1.2} array: we look for data in the first & second subrange

See Validity2Ranges.ods


(deleted for reasons named in my answer)

(As I wanted to add another remark, I converted my further comment to the answer by @PYS into a second answer.)

The validity range


should also do.
(The point as the column delimiter for inline arrays might conflict with the locale.The semicolon as the row delimiter should not. To avoid conflicts definitely yet another possibility:)


Is this (picking 2 isolated items from a column range) actually a good idea? I doubt it. You cannot adapt the validity without directly editing it if need arises this way. Using formulae to give a vaildity (“range”) should, however, allow to parametrise it (IMO). Otherwise we should use the ‘List’ option.