# 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 edit retag close merge delete ## 2 Answers Sort by » oldest newest most voted Hi If I understand the question correctly, you can use the following formula as a source of validity for E1: INDEX($B$1~$B$16;0;0;{1.2})  • ~ 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 Regards more ## Comments (deleted for reasons named in my answer) ( 2016-02-29 11:21:35 +0200 )edit (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 INDEX($B$1:$B$16;{1;16})  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:) OFFSET($B\$1;{0;15};0)


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.

more

## Stats

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

Seen: 44 times

Last updated: Feb 29 '16