How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Richard Your Own Question
Richard
Richard, Computer Support Specialist
Category: Computer
Satisfied Customers: 40227
Experience:  Over 15 years of consulting to the IT industry
32989067
Type Your Computer Question Here...
Richard is online now

I need to create drop down lists that allow you to select multiple

Customer Question

I need to create drop down lists that allow you to select multiple options. I am not very good at writing or understanding VBA code though
Submitted: 2 years ago.
Category: Computer
Expert:  Richard replied 2 years ago.
Thank you for your question, my name is ***** ***** I look forward to assisting you.
Please note, click on Reply to Expert to reply to me, a rating is only needed when we are finished.
You are trying to do this in Excel?
And what do you want the sheet to do once the code is selected?
Customer: replied 2 years ago.

Yes I am trying to do this n excel. I am creating a template that will allow users to click on a cell and to be presented with a drop down list. This part I am fine with. However, the user will often need to click on several options in the drop down list and for all options selected to be presented in one cell separated by commas

Expert:  Richard replied 2 years ago.
ok, can you provide me your sheet please via the below instructions
- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"
Customer: replied 2 years ago.

The file ID is 596230.

It is columns z to AM that I need to add the drop down lists to. sheet 2 shows data that should appear in the drop down list with the relevant headings.

Thanks

Expert:  Richard replied 2 years ago.
ok Darren
So you want this done for 14 columns instead of one now?
That is a lot more the the initial scope of work you indicated for just one column as this will all need to be coded and its quite a lot.
I have made a price alteration, if you accept the offer I can do all this for you.
Richard and 3 other Computer Specialists are ready to help you
Expert:  Richard replied 2 years ago.
thanks
Doing this now for you
Customer: replied 2 years ago.

Excellent, thanks Richard

Expert:  Richard replied 2 years ago.
ok, I do see an issue though, and that is multiple selection can only be done once on a sheet as it is using the worksheet change event which is only possible for one dropdown on the sheet.
Can we do it so you have drop downs on the rows under it, they can select as many as they want and it is displayed in one cell at the top?
Customer: replied 2 years ago.

Will the drop down list still be available in each column in the row? If so then that is fine.

Expert:  Richard replied 2 years ago.
This is now done for you Darren
You can download it from www.wikisend.com under File ID 396520
There is a button you click with Process on it, after the selections are made.
Let me know if you have any difficulties please
Customer: replied 2 years ago.

This overwrites the selection when i click on the selection. I already know how to do this. What I wanted is to be able to select multiple options from the drop down list and for the cell to record each selection separated with a comma and no spaces. e.g. In column AI additional resources, a user might want to select 2PC's and 1FSE, so if they select both options from the drop down the cell would be populated with 2pc,1FSE

Expert:  Richard replied 2 years ago.
No, you need to press the button after you have made all your selections in each row Darren.
Then it combines them into the Top cell.
But you press the button for this to occurr
Customer: replied 2 years ago.

That is what I am doing but it is only saving one selection. E.g In Column AI, I am selecting 1PC, 2FSE then click process but it is only saving 2FSE. I have tried selecting 1PC, then process, then 2FSE then process but again this only displays 2FSE. It is not saving the multiple choices that I am selecting

Expert:  Richard replied 2 years ago.
You need to do it row by row.
So row 3 a selection, then row 4, then row 5 ect
Then click the button
Customer: replied 2 years ago.

Got it. however, I need each row to be able to populate the same way as the top row does.

This template will be populated with 1000,s of tasks. For each task the user needs to be able to state what is needed for each task. Therefore, every row will be a different task and I need to be able to see if for each task for example how many FSE's, PC's and PO's are needed to complete the task.

Expert:  Richard replied 2 years ago.
That can't happen though as each row can only do one value. You can set multiple per a cell,
It is row by row, with the top row concatenating all the below row selections.
Customer: replied 2 years ago.

I have seen on a lot of websites it can be done, I am just rubbish with VBA so I have had no luck.

I just need it to show like this:

Row1 = 1pc,2fse,1po

Row2 = 2pc,1fse

Row3 = 3PC,1PO etc etc. Then to be able to do this for each column

Expert:  Richard replied 2 years ago.
As I initially explained, and as you see on the websites, it is only for ONE box as it is using the event change for the worksheet.
Which is why I said to you the alternative for multiple boxes, which you agreed and I coded.
Customer: replied 2 years ago.

So are you saying then that what I want can not be done, or is their an alternative option?

Expert:  Richard replied 2 years ago.
Correct and the alternative is what I did for you.
Customer: replied 2 years ago.

Oh man, so I have just spent £70 to be told that what I want can not be done?

Do you know of any other techniques that can be used to select multiple options?

The process that is currently being used to fill in these task sheets is to manually enter the inputs for each cell. However, the exact same spelling, spaces etc need to be used as the file is being uloaded to a new system which will not recognise the entry if it is not exactly as specified. Which is why i am trying to use pre defines lists to choose from to minimise the error.

Any suggestions on making this better would be most welcome

Expert:  Richard replied 2 years ago.
I dont't understand why when I explained what I was going to do, you agreed to it and got me to do all the work....
What your doing though is using Excel for activities it is not designed for.
It is an analysis tool, not a database or data entry tool.
If your looking a complex data entry you would want to look at Microsoft Access which is made to capture data and consolidate it ect.
Excel is for analysing data.
Customer: replied 2 years ago.

I misunderstood what you were asking.

This is frustrating as the top row does exactly what I want it to do. Is there any work around you can think of that will give me this for each cell. Can what you have done for the top cell just not replicated for each cell?How easy would it be to do this in access? I could then just export it from access to excel

Expert:  Richard replied 2 years ago.
No, if there was another way to do it I would have done it that way Darren.
You would need to create a database in Access, then the forms and develop this. You cannot just export it to and from Excel and Access.
Customer: replied 2 years ago.

So is that it then? There is nothing at all that can be done to give me what I want?

Expert:  Richard replied 2 years ago.
yes, What I did for you.
In excel thats as far as you can go.