Ask Your Question

dropdown box with two items

asked 2016-02-29 04:26:09 +0200

n9mfk gravatar image

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-02-29 09:49:17 +0200

pierre-yves samyn gravatar image


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


edit flag offensive delete link more


(deleted for reasons named in my answer)

Lupp gravatar imageLupp ( 2016-02-29 11:21:35 +0200 )edit

answered 2016-02-29 11:42:14 +0200

Lupp gravatar image

updated 2016-02-29 11:46:02 +0200

(As I wanted to add another remark, I converted my further comment to the answer by @pierre-yves samyn 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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-02-29 04:26:09 +0200

Seen: 44 times

Last updated: Feb 29 '16