Formula if an item from a list is selected in cell 1, then don't display in cell 2

**Silenz** · 05-26-2013, 12:28 PM

Hello everyone !

My problem is the following:

I have a list of items (ex: cells C1:C10) and named that list -> "list1"

In cell 1 I introduced that list with data validation, so in cell 1 it appears items from 1 to 10.

What formula should I use in cell 2 to display the items from list1, but when in cell 1 item 1 is selected, then in cell 2 to appear items from 2 to 10, or if item5 is selected in cell 1, in cell 2 to appear items from 1 to 4 and from 6 to 10, etc.

So basically when an item is selected in cell 1 from that list, in cell 2 to appear the other items, without the item selected in cell 1

Can anyone help me?

**benishiryo** · 05-26-2013, 08:47 PM

hi Silenz, this video might help.

http://www.youtube.com/watch?v=1t4yl7P7-9A

**Tony Valko** · 05-26-2013, 09:51 PM

Try this...

Let's assume C1:C10=

Item1
Item2
Item3
Item4
Item5
Item6
Item7
Item8
Item9
Item10

Enter this array formula** in D1 and copy down to D10:

=IFERROR(INDEX(C:C,SMALL(IF(COUNTIF(A$1:A$10,C$1:C$10)=0,ROW(C$1:C$10)),ROWS(D$1:D1))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This new list in D1:D10 will be the source for the drop down lists.

Now, setup the drop down lists...

Select the range A1:A10
Data Validation
Allow: List
Source: =OFFSET(D$1,,,COUNTIF(D$1:D$10,"?*"))
OK out

Formula if an item from a list is selected in cell 1, then don't display in cell 2

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Formula if an item from a list is selected in cell 1, then don't display in cell 2

Re: Formula if an item from a list is selected in cell 1, then don't display in cell 2

Re: Formula if an item from a list is selected in cell 1, then don't display in cell 2

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions