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

[adhide]

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

[Richard Buttrey]

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.

[adhide]

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?

[mlcb]

Hi,

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

[mlcb]

Correction:

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

[adhide]

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

[adhide]

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

[mlcb]

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)

[adhide]

Hi mlcb

THis will do.

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

[mlcb]

Hi mlcb

THis will do.

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

You are welcome. Glad for helping

[adhide]

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.

[mlcb]

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?