Paste value(s) from column in inactive worksheet in same cell

**Stkbm** · 06-27-2014, 11:38 AM

I have the following 2 worksheets in a workbook
Issues (Sheet1)
IssueID (ColA) - Protected formula
Type (ColB) - Drop-down menu
Driver (Col C)
Description (Col D)

Controls (Sheet2)
ControlID (Col A) - Protected formula
Type (Col B) - Drop-down menu
IssueID(s) (Col C)
Description

This workbook goes to users in every division on a quarterly basis, and they are responsible for self-identifying issues relevant to their business processes. When the user selects the Issue.Type, the Issue.IssueID auto-populates to adhere to a pre-established naming convention.

In the Controls tab, I need for the user to have the ability to select from all Issue.IssueID's listed in the Issue tab. Because a single control can address multiple issues, they need to be able to add multiple Issue.IssueID's to the same cell, using one of two formats (comma separated or a carriage return) between multiple values. At the same time, because there is a fomula on the target column of data, I do not want to pull the formulas into the drop-down list the user selects from.

I know you can
- enter multiple values from a menu into the same cell, and
- reference ranges in inactive sheets...

...but I don't have a clue how to put all of the pieces together. I am a beginner at Excel VBA, so I thank you in advance for providing as much help as you possibly can.

**Bernie Deitrick** · 06-27-2014, 01:12 PM

Name the issue range on the Issues tab "IssuesList"

Then use DV on the controls tab for the cells where you want to enter the issues, setting the DV to List, and using =IssuesList as the source.

To enter multiple values in one cell from the issues list, with a comma between, copy this code, right-click the sheet tab, select "View Code" and paste the code into the window that appears. Save the workbook as a macro-enabled .xlsm if you are using 2007+ format.

Please Login or Register  to view this content.

**Stkbm** · 06-27-2014, 02:41 PM

Thank you for the prompt response. While I work to implement this solution, I have another question. Because I am working with others to determine what will work best for the end users, I was asked if I could create a combo box 'on the fly' from the same column of data - which would then allow users to select multiple items at once - and then have it post to the cell in the same manner (comma delimmited), as opposed to having a user click the drop-down multiple times to select each related ID.