Hi,
I have a table with 2 columns. In column A I have regions (Asia, Europe, Africa) and on column B names of companies associated with countries. How do I create a dropdown list that shows the company names only for only a certain region. Everything is mixed by the way...
Thanks in advance!
Create a new list grouped by regions and then apply dependent lists per the instructions here:
http://www.contextures.com/xldataval02.html
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
You could also create a pivot table which allows you the option to drop down information. Simply highlight the ranges of the two columns and click on Insert-->Pivot Table. Put the regions in the "row" section and the countries in the "Column section. There you will be able to drop down each region to show which countires fall under them.
Cheers
Thanks for the interesting replies. However, creating different region lists is a bit messy as everything is mixed and regions are not separated. They won't be separated either.
Pivot tables are good for filtering but unfortunately they don't display the results in cells as data validation tables do. I could filter somehow things with pivottables but that's not an option for the setup I have now.
What to do then. It is a simple validation list just that is dependent on a condition...how? anything that I'm missing?
Not 100% sure I can help but it is always best to supply a sample workbook showing a sampling of what you have and what you want to do. This will ensure we understand your situation and can suggest more accurate possibilities.
See if this conversation helps:
http://www.officekb.com/Uwe/Forum.as...-unsorted-list
Here you go.
Imagine that on cell A1 I define the region AS.
So, I want to be able to list the companies on a cell that satisfy the condition AS.
Doable?
is it something like this you're looking for.
I inputted Regions starting in A2 and Companies starting in B2. In A1, I created a Data Validation List (in xls 07, Data/Data Tools/Data Validation/Data Validation Setting/Validation Criteria/Allow: Select "List"/ Source: Select the range you want to be referenced, in our case A2:A4, you can expand it as you wish.
In B1, I added a vlookup which looks for the region you select in A1 and it searches it in table_array $A$2:$B$38 (again expand accordingly, if your list is bigger). So each time you select a region in A1, B1 will return the corresponding company.
Does that work for you?
If I helped, Don't forget to add to my reputation (click on the little scale)
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
Not sure if this is what you are looking for...
See attached.
So if you have an input cell in say, G2 with List of Regions.
Then we can find records matching using helper formula in D2:
=IF(B2=$G$2,COUNT(D$1:D1)+1,"")
copied down.
Then to extract those records somewhere off to the side, say in N1 enter:
=MAX(D:D)
and in N2:
=IF(ROWS($A$1:$A1)>$N$1,"",INDEX($C$2:$C$32,MATCH(ROWS($A$1:$A1),$D$2:$D$32)))
adjust ranges to suit and copy down as far as you want.
Then create a dynamic named range....
Go to Formulas|Define Name and enter List in the Name field and formula:
=OFFSET(Sheet1!$N$1,1,0,COUNTIF(Sheet1!$N:$N,"?*"),1)
in the Refers to field.
Then set data validation in H2 as List with formula =List.
meyero90. Exactly that, but I have many companies for a certain region. How's go about that in order to create a list with data validation. Check my example.
vasco
Yes, NBVC!That is very close to what I wanted. However, I would love to eliminate the need to paste values in those columnd D and N. Do you think that would be possible?
You can perhaps eliminate column D and use an array formula in Column N.. but you need at least column N, in order to formulate the array to include in your data validation....
OK. I'd rather go for a PivotTable actually for what I understand. I inserted in G4 a PivotTable that enbales you to select the Regions by simply filtering out the data and the corresponding companies will show up according to the regions you selected. In my example I selected regions AF and AS.
Last thing in order to filter the data you want to show up, simply click on the filter of "Row Lables" (that you can rename at your convenience) and select the filters you want to apply to the data set.
In case, you expand your data, please make sure to change the source of data following these steps:
1. Click anywhere in the pivottable
2. Click on the highlighted Red sections that just appeared in your toolbar ribbon called PivotTable Tools
3. In Options, Click on "Change Data Source", and expand/shrink your data source.
4. This will re-adjust the results in your PivotTable.
Is this what you're looking for?
Last edited by meyero90; 08-16-2010 at 11:26 AM. Reason: more info on the topic
meyero90, i can see your solution working, but I am making an excel sheet for work that is going to be automatized for years to come. My idea is that by removing steps you reduce the chance of making errors. Thefore NBVC's solution is good. However, I still need to eliminate that column N. Any ideas?
If not, maybe we need a competely different solution. Going back to basics. Which formulas allow me to list an array of values rather than a single value? Maybe they can be used in a list later on?
Why can you not use column N? It can be placed way out in a lone column or it can be placed in it's own sheet.
I don't think you can use a formula in Data Validation that will condense your list from your current column C based on the condition of the first option.
In order to have a list, you need a range with the values for the list (or hardcode them in the DV dialog). No other options, unless you use VBA to create the hardocded list, and then you are limited to 255 characters. Rearranging the worksheet would make life a lot easier for you I think.
Remember what the dormouse said
Feed your head
Just that I don't like to have cells in between just serving as an intermediary step when this could be done in 1 direct step with no need to paste these values in between.... There must be a way around this....
Why must there be?Originally Posted by vascobmcastro
Just a hope...starts being a desperate hope though!
There isn't. As I said, if you want a dropdown, then you need a range or a hardcoded list. No other options exist for data validation. You could use controls on the sheet, but I would strongly suggest rearranging your sheet instead.
last desperate question:
The OFFSET function is for allowing a dynamic size list that matches the contents of column N.
If you don't mid blanks, then you can just select a fixed range in column N.. that would include blanks at the bottom and name that list "List".
It can be done with INDEX function if you don't want OFFSET, but it would still be a nested dynamic range formula.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks