+ Reply to Thread
Results 1 to 11 of 11

How to select multiple data from a list based on different headings

  1. #1
    Registered User
    Join Date
    08-24-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    36

    How to select multiple data from a list based on different headings

    Not sure how to write this, but I have a table consisting of two colums that I need to draw data from based on users selections.

    For example, there is a colum with names and a heading, and I need to have a slector (drop down or whatever) that first the user selects the catagory (i.e 'Mining'), this then allow the user to slesct from another list, only the descriptions relating only to that list (i.e. 'Copper Ore Mining') and then in another area of a form enter the relating code (I.e. '1313')

    Not sure if im making sence or if this is layed out wrong to start with.

    Any suggestions to a new Excel user with moderate experience.

    Any guidance sought
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to select multiple data from a list based on different headings

    Hi,

    Yes you hit the proverbial nail on the head. For tasks like this the data should be laid out in a normalised 'database' type structure where every row carries values for a particular field.

    Once you have a proper layout then subsequent analysis is much simplified. And for quick analysis and totalisation (although not in the example) a Pivot Table gives you the ability to filter, dice and slice your raw data.

    See attached. Use the drop down in B2 and the Pivot table will filter automatically. Note how the data carries the code for each row.
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    08-24-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: How to select multiple data from a list based on different headings

    Pivit tables are something I have not mastered. However, my delema is that I will have on sheet1 a form that I need people to go through and doe the following:

    For example:
    B5, select industry = Mining, Manufacturing or what ever;
    in B6, select the relevant sub catagory, i.e Copper Ore and then
    in B7 enter the code associated with that = 1313

    The way i see the Pivit table is that I need to have all of the data visable on the form that is to be printed etc.

    Any further suggestions?

  4. #4
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to select multiple data from a list based on different headings

    Hi,

    If all the codes of the category A begin with 0; Category B, with 1; category C, with 2....maybe something like this.
    Marcelo Branco

  5. #5
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to select multiple data from a list based on different headings

    Correction:

    ADMIN F3
    <---A2:D10 = Named range Category (gray)

  6. #6
    Registered User
    Join Date
    08-24-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: How to select multiple data from a list based on different headings

    Im confused but other than switching the code with the look up of the discritipon to then select the code, all goo. What I cant work out is how you got here

  7. #7
    Registered User
    Join Date
    08-24-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: How to select multiple data from a list based on different headings

    Full report attached to identify the 400+ catagories and why i need to narrow with drop down lists to make selection easier for users.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to select multiple data from a list based on different headings

    See if this is what you need

    (to make the formulas easier, note that I inserted the word END at ANZSIC A487 and at ADMIN B19)
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-24-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: How to select multiple data from a list based on different headings

    Hi mlcb

    THis will do.

    All I now need to do is teach myself how. THank you.

  10. #10
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to select multiple data from a list based on different headings

    Quote Originally Posted by adhide View Post
    Hi mlcb

    THis will do.

    All I now need to do is teach myself how. THank you.
    You are welcome. Glad for helping

  11. #11
    Registered User
    Join Date
    08-24-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: How to select multiple data from a list based on different headings

    I am building my form around the lists and realise to make sure users do not just submit or print off the form without ensuring they select a relevant catagory and description, I have added a row to have a name for this purpose called 'Select...' in the catagory and Description and adding a row in the Admin area.

    The only issue is when I do that, I cannot change the dropdown in the Description it doesnt give me any options.

    Is there a way to reset the form to a default look (to force the users to select data). What appears to be happening is the Description drop down doesnt recognise the new catagory 'Select..' and or does not make a match and does not allow any changes to the selection associated with the Select catagory.

  12. #12
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: How to select multiple data from a list based on different headings

    Quote Originally Posted by adhide View Post
    I am building my form around the lists and realise to make sure users do not just submit or print off the form without ensuring they select a relevant catagory and description, I have added a row to have a name for this purpose called 'Select...' in the catagory and Description and adding a row in the Admin area.

    The only issue is when I do that, I cannot change the dropdown in the Description it doesnt give me any options.

    Is there a way to reset the form to a default look (to force the users to select data). What appears to be happening is the Description drop down doesnt recognise the new catagory 'Select..' and or does not make a match and does not allow any changes to the selection associated with the Select catagory.
    Sorry, I'm not following you on this. Could you attach your new workbook? I have to leave now but I'll take a look latter.

    To reset the drop-downs *maybe* you need VBA (Worksheet_Change Event). Is it acceptable?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Select Multiple Sheets based on List
    By roasty_1 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-22-2014, 01:36 AM
  2. List multiple Column Headings
    By Dalj in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-19-2010, 06:44 AM
  3. List box with multiple columns and headings
    By Khaos176 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-19-2009, 04:31 PM
  4. data validation list should have opt. to select based on criteria
    By be in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-15-2005, 09:05 AM
  5. Extracting/look up data from a list and select multiple instances
    By Candice H. in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-29-2005, 12:06 PM

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