Excel assign columns to groups.

Good day all.

I am trying to figure out a way to correlate data in a column to one cell.

I'm creating a departmentalized list and need to assign their vendors by department use. So what I have is on one sheet several columns, they are just list.

For example I want to assign each column to a group, say Column A to cell Y1 'Food', Column B to cell Y2, 'Maintenance' and so on... What I will then do with these cells in Y column is assign them to a drop down list. Then on another Sheet in the same workbook I will choose from that drop down list and below where I designate the area to be filled with the data, the data from said choice from Y1, Y2, etc will be displayed.



I'm using Excel 2016

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Are you saying, for example, that you will want to create a dropdown list linked to cell Y2 (Maintenance)? Excel sees cell Y2 as a single data point and won’t separate the items inside it to accommodate a dropdown list. There are probably other ways to get to where you want to go but we need to see how your data is set up and exactly what your end goal is.

I uploaded an example to my original post.*

Sheet 2 has the list in columns a b c d and my drop down list is in column g. Sheet 1 has a drop down list that is linked to Sheet 2 Column G. I want to assign the list in Column A to G1, Column 2 to G2, etc... So that on Sheet 1 when G1, G2, etc... is choosen from the drop down list, the data that is assigned willl populate to a designated below column or area I choose.

We have an extensive vendor list and several departments with other bits of information we are pulling in from other areas via the connections option, that part I have working. I just cant quite figure out how reference the data, rather than doing something like "=Sheet2!A24" which if I have to I can. It will be several pages if I merge all the data together. Which is if I do the drop down will only populate the needed information for that department. So if I can say assign an array / table to a cell and just have that data populated by choosing from the drop down list, it would be simpler.

Is this what you are looking for?
I named each list in Sheet2 Columns A:D (List1,List2, List3,List4). Defined names can’t have spaces.
Then in Column B (B2:B10), I used Data Validation>List> Source: =INDIRECT($A2)

Questions?

Is the attached what you're looking for? There would need to be some cleanup, likely, to get it to function exactly the way you want, but is the general concept what you're looking for?

Either dependent dropdown/data validation or whole list:
Named ranges:
list_1 =Sheet2!$A$1:$A$16
list_2 =Sheet2!$B$1:$B$12
list_3 =Sheet2!$C$1:$C$8
list_4 =Sheet2!$D$1:$D$16
List_Of_Lists =Sheet2!$G$1:$G$4
----------------
dependent dropdown formula in B2:

Please Login or Register  to view this content.

----------------
print the whole list col D:

Please Login or Register  to view this content.

This is so much neater using PowerPivot.

Add the following named ranges:
MyData =Sheet2!$A$1:$D$16
MyLists =Sheet2!$G$1:$G$4

Now add a query (using Get & Transform Data):

MyData (close and load to Data Model):

Please Login or Register  to view this content.

In PowerPivot, Select column 'Values', and select 'Sort by Column > SortOrder'. Now you can hide SortOrder from client tools.

Finally, add a pivot table to sheet 1, using the Data Model as the data source, with List in Filters, and Value in Rows. Format to suit.

A nice neat solution, which can easily scale, by simply managing your named ranges, and refreshing the data.

See https://excel.solutions/xlf_1272942/ for a worked example file.