List those which are selected

[Bhuktar S]

I have 40 item names in column A.
Column B,C,D are for entering qty. (max.) 1 for items in A.
Column B, C & D cells are validated such that not more than 6 items ca
be selected per columns B, C & D (so, total max. 18)These 6 can be an
out of the 40 items.
in cells 41st to 46th of columns B, C & D, I need to list the names o
those 6 selected, but 1 in each cell one below the other.
What is the formula for this?
A-----------B---------C----------D
Item1-----1---------1----------1
Item2-----0---------1----------0
Item3-----1---------0----------0
and so on

Result-----Item1---Item1----Item1
Result------""-------Item2-----""
Result-----Item3----""---------"

 

[Dick Kusleika]

Bhuktar S

=IF(B1>0,$A1,"")

and fill to the right and down as needed.

 

[Frank Kabel]

Hi
seeing Dick's anwer I'm just not sure if you also want blank cells in
between in your result?. If yes, Dicks solution will work perfectly for
you. If you ONLY want to show the checked items per column just post
back

 

[Bhuktar S]

Sorry, that is not the solution.
Out of 40 items, ANY items are selected (max. 6). Then in the last
cells (cell 41st to 46th) of respective column, these selected item t
be listed.
Say, if item nos. 2,5,11,19,20,38 are selected in B, then:
B41=name of item no. 2
B42=name of item no. 5
B43=name of item no. 11
B44=name of item no. 19
B45=name of item no. 20
B46=name of item no. 38

If item nos. 3,11,14,25 are selected in C (only 4 are selected),then:
C41=name of item no. 3
C42=name of item no. 11
C43=name of item no. 14
C44=name of item no. 25
C45=(Blank)
C46=(Blank)

(Note, item 11 is selected in both B & C but does not get listed in th
same row of B & C)

 

[Bhuktar S]

Would like to inform further, that rows 47th onwards are used for othe
informations/data, so available cells are only 41st to 46th

 

[Frank Kabel]

Hi
enter the following array formula (entered with CTRL+SHIFT+ENTER) in
cell B41:
=INDEX($A$1:$A$40,SMALL(IF(B$1:B$40=1,ROW(B$1:B$40)),ROW(1:1)))
and copy this down and to the right

 

[Bhuktar S]

Dear Frank,
There may be some error/missing in the formula you suggested, it doe
not work. Secondly, what does SMALL doing? and what is the meaning o
Row(1:1)?
Please note, any 6 items will be selected from the 40 items.
Thanks for your help

 

[Frank Kabel]

Hi
have you entered this formula with cTRL+SHIFT+ENTER?

Small will return the nth smallest value. n defined by ROW(1:1)=1

 

[Bhuktar S]

Thank you !
Yes, that was my mistake not entering with ctrl+shift.
Result for not selected is #NUM!. How do I avoid that? I want th
result blank.
Secondly, could you explain the formula? Such things are not explaine
in Help file or so. Please recommend me to read such things from we
site.
Thanks again

 

[Frank Kabel]

Hi
for preventing errors use:
=IF(ISERROR(INDEX($A$1:$A$40,SMALL(IF(B$1:B$40=1,ROW(B$1:B$40)),ROW(1:1
)))),"",INDEX($A$1:$A$40,SMALL(IF(B$1:B$40=1,ROW(B$1:B$40)),ROW(1:1))))
also entered with CTRL+SHIFT+ENTER