How to make this kinda dropdownlist?

[peddewop]

Okay..I watched dozens of video's emailed with someone who makes those vids...but still haven't got an answer. I would love to upload a screenshot but I can only post a link(?..*gets more frustrated knowing he can't get a clear explanation and answer in one simple try*)

I will type an example of what my excell sheet looks like (since i cannot post the screenshot [ok ok..now we know]). then I will explain what I want to do. ..I just hope after posting the text will be in the same position as I typed it...Anyway here it goes:

A B C D E F
1
2
3 Country Company Status
4 UK 1 C
5 UK 7 V
6 Japan 3 V
7 UK 6 X
8 Japan 2 X
9
10


- The red words are the names of the columns (country, company, status)
- In this example the rows are arranged in order of status (alphabetical)
- The cells with the name of the column (the red words) should contain a dropdown list.
-This dropdown list should contain the data in the column below. So for 'country' this is UK and Japan, for 'Company' let's say 1 to 10 and for 'Status'; C, V or X
- I wanna be able to select a country so the list gets shortened to all the rows with the selected country in that particular column.
- I also wanna be able to select on status..so when I choose X in the dropdownmenu below the header 'status' in D3 I only wanna see UK comapny number 6 and Japan company number 2.
- As you can see data is entered horizontally and should be fixed in that terms of speaking. (hope you undestand what I mean here)
- Last but not least; after selecting on lets say status x i also wanna be able to put the companies in alphabetical order (a-z / z-a / 1-../ ..-1). So I wanne be able to re-arrange rows by puting the countries or the companies in alphabetical order after selcting for example status x. I understand you actually re-arrange the column from A-Z not the row..but again I hope you understand what I mean here.

Hope this is clear..if not, let me know how to post a printscreen. You can also email me then I will send you by email. Removed e-mail address - Moderator

Thnx in advance!

Regards

 

[VoG]

Hello and welcome to MrExcel.

To me your explanation is confusing but I think that you want Dependent Validation Lists.

 

[peddewop]

I just took a quick look..I'm afraid this isn't it. i just want to be able to rearange and shorten a list (table with several colums of info).

So the name of the column should have a drop down list in which I can select one of the words/numbers or whatever data is in that column in order to see a table with only the info selected.

This way you can have endless collums and rows (enourmous table) filled with data but always be able to find specific data (read: not only find but.. but find and ONLY reproduce the specific data[and the data of other colums that belongs to one entry]) . Like of all the countries in column B only the UK coutries. Or find all the companies with the same status by selcting on status. And again of course you wanna see the rest of the companies information (selected on status in this example) as well..so you should also see the country and the name of the company..not only a list of 10 times a status without any info. that would be useless..

In the older version (2000 I think) you could easily make this kinda re-arrange buttons....just by right clicking and do some little trick. Don't ask me how....but i've already done it..now opening my files in a newer version and save it again..it's lost. And nobody ever heard of this function... It's getting frustrating...coz the things explained in the vids should be far more complicated....

oh yeah..and once reproduced a list with for example only UK countries (the rest not shown) I also wanna be able to re-arrange any of the colums (country, company or status in this case) in alphabetical order.

Anyone???

How can I post a screenshot? Why is my email address been erased? I wanna post my problem in a way you are able to visualize what i'm telling...hmpffff

..guess my first post should do.. if the 'excell sheet' wasn't displayed so messed up..

 

[crimson_b1ade]

Use Excel's AutoFilter feature. On the toolbar it's Data\Filter\then AutoFilter.

 

[peddewop]

<SCRIPT type=text/javascript> vbmenu_register("postmenu_1785015", true); </SCRIPT>Crimson b1ade!..thank you. It's actually even easier then before..

I was thinking about re-ararranging in alphabetical order..but as far as I can think of now (with my sleepy head) it was just kinda bug in the old system which made it necessary...don't think I will need it anymore..I'll figure that out tomorrow. I know I can always use the a-z button...that's not it..

well at least i know tomorrow will be less stressful....last but not least;how is it actually called? Just a filter? I knew somehow drop-down list was not completely the right term..or can't it be even named so?

Anyway thnx...you made my christmas night haha. Everybody merry christmass and happy new year!