Combining selections from drop-down lists into a separate drop-down list

Okay. I hope that someone will be able to help me. I'm desperate now!

In one workbook, I have 2 worksheets (OBJECTIVES, ACTIVITIES).

On both worksheets there is one field that is the same, which links the data on both (GROUPID).

On the OBJECTIVES tab, there are 5 columns (not side-by-side) that have drop down selections in them (tied to each specific GROUPID, one row per GROUPID). At any point the selections may change.

On the ACTIVITIES tab, there can be multiple rows with the same GROUPID (not in any particular order) since there will be mutliple activities per GROUPID. One column is the "objective" column.

In this "objective" column on the ACTIVITIES tab, I need to have a drop down list that has all the selections chosen for that particular GROUPID from the OBJECTIVES tab. Each time that a GROUPID is added in another row (regardless of the order), I need the "objective" drop down field to reflect the chosen selections for that GROUPID (from the OBJECTIVES tab). Each activity needs an objective chosen; multiple activities can have the same objective chosen (and only those selected for that GROUPID to show up in the drop down list).

I am absolutely stumped. I couldn't figure out how to combine selections into a new drop down list (while matching by a specific identifier).

I hope that someone will be able to help me. Thank you!!!

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook. Don't upload a picture when you have a workbook question. None of us is inclined to recreate your data. Upload the workbook and manually add an 'after' situation so that we can see what you expect. In addition clearly explain how you get the results..
To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.

You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file.

To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Can you update your example ... provide an additional sheet which reflects the desired outcome based on the sample data.

Regards, TMS

To create drop down lists without VBA, the "range" of cells holding the values to display need to be congruent. I've added formulas in S:W on the Objectives sheet to pull all the objectives into a joined range for each row, so the drop downs can be created from there.

Then I selected F2 (important step) before I created the named formula MyCodes (press Ctrl-F3 to view it). This dynamic formula is spotting the column A GroupID on the Objectives sheet, then offsetting to column S of that row, then displaying the values in those 5 columns on that row.

Then Data Validation in column F is using the new MyCodes dynamic formula to display the results for each row. Notice there is no drop down in F2 because MOTOR is not on the Objectives sheet.

Now that it is working, you can hide column S:W if you wish.

What zeros? You'll have to show me the problem.

On your speadsheet (and mine)... If nothing is chosen, the linked field shows a zero '0'. You can see this on the Objectives tab if you scroll down to the linked rows (starting with row 12, columns S-W) that have nothing in the original source fields. You can also delete a few of the selections in Column M or P. The corresponding linked field will show a zero.

Again, thank you!

This is not simple step up in complexity. The formula in S2 was:

=D2

....and now, to suppress zeros and give drop down lists without zeros showing, the formula changes to the array formula:

=IF(COUNTA($D2,$G2,$J2,$M2,$P2)<COLUMN(A$1), "", INDEX($A2:$P2, SMALL(IF(LEFT($A$1:$P$1,3)="OBJ", IF($A2:$P2<>"", COLUMN($A$1:$P$1), ""), ""), COLUMN(A$1))))

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array.

Then that cell is copied to the right. THEN, copy all 5 cells and paste downward.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.