Drop down list, selecting multiple items

[Kiwi den]

Hi all,

I amlooking for info on how to set up a drop down box, that will allow the user to select multiple options from the list.
Can anyone point me to a solution please

I would like cell A:1 to have drop down and a list of items appears, if on is selected then this appears in A:2, if another is selected it will appear in A;3 and so onhopefully no more than 15 items per list. I see the list being on a seperate sheet.


Tanks in advance

 

[My Aswer Is This]

I do not understand:
"the list will come from the general scope of work (in the left),"

But if it works for you that's great.

 

[mtugahan]

I mean this list on the left should appear in the drop down list below each header. I'm still struggling on this

I do not understand:
"the list will come from the general scope of work (in the left),"

But if it works for you that's great.

 

[My Aswer Is This]

You said you had a drop down list but you did not say where the drop down list was.
So I gave you a example if the drop down list was in Range("E1") then you said you wanted this to work in multiple drop down list. But you never said where your drop down list were.

You need to give me exact details or be able to modify the script.

Are you saying you have the drop down list in row(2)

How should I know where your drop down list are if you don't tell me.
And are you installing this script in your sheet?

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

 

[My Aswer Is This]

You now are saying:
"I mean this list on the left should appear in the drop down list below each header"

So your wanting a script written that will load values into your Drop down list:

That is not what your Subject title says:
It says:
"Drop down list, selecting multiple items"

If your wanting a script written that will create a drop down list for you and load all the values into the drop down list then I do not know how to do that..

 

[L. Howard]

I am as confused as My Answer Is This.

I think you want three drop downs House1, House2, House3 and you want to select items from the list you show in post #9 . And you want the selected items to be listed below each header, H1, H2, H3. And you want the repair item to be selectable ONLY ONCE.

If all that is true, then do this for a test sheet which will duplicate mine. It is a bit complicated. Use Sheet1 and Sheet2.

On Sheet2:
Copy the list on post #9 and paste it on sheet2 in columns A, I & Q.

cell B1 enter > =IF(COUNTIF(Sheet1!$B$2:$B$12,A1)>=1,"",ROW())
cell J1 enter > =IF(COUNTIF(Sheet1!$I$2:$I$12,I1)>=1,"",ROW())
cell R1 enter > =IF(COUNTIF(Sheet1!$P$2:$P$12,Q1)>=1,"",ROW())

Pull the three formulas down to the end of the repair items lists

Still on sheet2:
cell C1 enter > =IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$12),"",INDEX(A:A,SMALL(B$1:B$12,1+ROW(A1)-ROW(A$1))))
cell K1 enter > =IF(ROW(I1)-ROW(I$1)+1>COUNT(J$1:J$12),"",INDEX(I:I,SMALL(J$1:J$12,1+ROW(I1)-ROW(I$1))))
cell S1 enter > =IF(ROW(Q1)-ROW(Q$1)+1>COUNT(R$1:R$12),"",INDEX(Q:Q,SMALL(R$1:R$12,1+ROW(Q1)-ROW(Q$1))))

Pull these three formulas down to the end of the repair items lists.

Still on sheet2:
Name the columns C, K, S:

With the mouse, select C1 to C12 > Formula tab > Defined Names > Defined Name > Define Name... > New Name box > Name window > House_1 > Scope window > Workbook > Refers to: > =OFFSET(Sheet2!$C$1,0,0,COUNTA(Sheet2!$C$1:$C$12)-COUNTBLANK(Sheet2!$C$1:$C$12),1) > OK.

Now do the same for the other two columns, K and S with the names House_2 and House_3. Using these last two formulas in the Refers to... window.
(Be sure to use name exactly as shown, with the underscore _.)

=OFFSET(Sheet2!$K$1,0,0,COUNTA(Sheet2!$K$1:$K$12)-COUNTBLANK(Sheet2!$K$1:$K$12),1)
=OFFSET(Sheet2!$S$1,0,0,COUNTA(Sheet2!$S$1:$S$12)-COUNTBLANK(Sheet2!$S$1:$S$12),1)

Go to sheet 1.

In B1, I1 and P1 enter your house 1, 2, 3 Headers as you wish them to be.

Now select B2 down to B13 and Data tab > Data Validation > Allow > List > Source window > =House_1 > OK.

Do the same for columns I and P cells 2 to 13 with I columns. I source window =House_2, P source window =House_3.

You can now select from the drop downs starting with the first drop down below the header and then the next etc., so your list of repair items will be right below the header. And you can only select a repair item once. Once you make a selection, that item is removed from the named list on sheet2 for that House. Select all 12 items and there will be nothing more to select. Notice you can select any item from top to bottom from any of the drop downs.

To reset a House column, just select all the entries under a House header and delete them. You are loaded and ready to go again.

Howard