+ Reply to Thread
Results 1 to 8 of 8

Excel assign columns to groups.

  1. #1
    Registered User
    Join Date
    04-17-2019
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    3

    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
    Attached Files Attached Files
    Last edited by billtx; 04-17-2019 at 11:40 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    24,763

    Re: Excel assign columns to groups.

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    Are you new to Power Query and don't know what to do with the code you've been given? Have a look here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    MS 2007, 2010
    Posts
    12,336

    Re: Excel assign columns to groups.

    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.
    ChemistB
    My 2Ę

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    04-17-2019
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    3

    Re: Excel assign columns to groups.

    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.
    Last edited by billtx; 04-17-2019 at 11:51 AM.

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    MS 2007, 2010
    Posts
    12,336

    Re: Excel assign columns to groups.

    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?
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-17-2019
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    3

    Re: Excel assign columns to groups.

    Not really. Do you remember the way web pages used to be with Frames? What I am looking for is somewhat like a frame. I want to be able to select from the drop down list. And what ever that item our of that list correlates to will display below the cell the drop down box is. Its like the webpages used to use Frames.

  7. #7
    Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    269

    Re: Excel assign columns to groups.

    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?
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as Solved using the Thread Tools menu at the top of the thread.

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO200;Win10/MSO2016
    Posts
    10,221

    Re: Excel assign columns to groups.

    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.
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1