Return multiple values

[gbecky3]

I have two columns A1-A51 contains 4 Region names, B1-b51 contains the states that relate to the region, I would like to extract the states in to a separate column that match the region, so for example: if column C1 = West
I want Column D to contain all of the states that pertain to West
Col A Col B Col C Desired Results Col D
West OR West OR
West WA WA
West CA CA
Central TX
Central OK
Central MN
etc...

found this formula, but doesn't work and yes I am entering with CTRL,SHIFT,ENTER

{=INDEX($A$1:$B$67,SMALL(IF($A$1:$A$6=$C$1,ROW($A$1:$A$67)),ROW(1:1)))}

I am open for suggestions, seems simple enough, but can not figure out why the above will not work.

 

[Aladin Akyurek]

You probably need something like:

http://www.mrexcel.com/forum/showthread.php?t=231882

 

[hkaplan2]

If I understand your request, maybe a pivot table will get you what you want.

a PT can put the Regions in one column and the corresponding states in the next column, like this:

Excel Workbook
	A	B
1	*	*
2	*	*
4	Region	State
5	Central	MN
6	*	OK
7	*	TX
8	West	CA
9	*	OR
10	*	WA
Sheet4

If this is your goal, in the PT layout screen drag the region field name to the row area and the state field to the row area, and the state field also to the data area. Turn off Grand totals in the options button, and hide the count column and you get the above table.

Does this help?

 

[gbecky3]

it is my goal, but I do not want it in a pivot table, I am going to use the result colum as a lookup reference from another page with a "Form Control drop down list).

 

[hkaplan2]

You can always copy the PT to another range as straight text, and use that table, if that works for you.

Just a thought.

Also, are you trying to create a lookup table based on a value in another cell?. Such as if A1=West, then the dropdown list populates with just West states? If so, you can use the Indirect function in a data validation formula.

Just guessing here what you are working to accomplish.

 

[gbecky3]

that's exactly what i am trying to get at. On the Main page there is a Form dropdown with Region (West,Central,NE,SE), then when the user selects West etc...I just want the West states to show up in the next dropdown which is for States... (these results are then used to populate a pivot table I have on another page using VBA code). I have figured out how to get Data Vaidation and offset to work, but is there a way to do it using the Form Control dropdown, (I don't think so), so if I use the Data Validation, I would then like to be able to copy the dropdown list of states to another cell, then use that cell for my Form Control source. (which drives the pivot tables)

 

[hkaplan2]

I got a little lost following your plan. Not sure what you mean by the results populating a PT. Sorry.

But maybe this will help. You can use a regular cell data validation instead of a form control to do the first stage:

(typically on a hidden sheet) you create the region list, i.e. East, West, North, South. That obviously populates the first dropdown. Give that dropdown cell a range name like "region".

Next, create each regional list of states and give each list the range name of the region, i.e. NY, NJ, CT, MA, VT, ME = range name "East". Do this for every regional state list.

In the state drop down validation formula, use the formula =INDIRECT(region).

Based on the selection in the region cell, the state cell will display the associated states.

The selected value in that cell (some state selection) can be used just like the value in a form control in your VBA.

 

[gbecky3]

I know it is confusing to explain, what you said sounds like it would work, I will give it a try, thanks a bunch for your input

 

[gbecky3]

Ok, this may work, so what would the code be if I am not using a Pivot Table
Above you said I can use a validation drop down list.
This is the current code which works with Pivot Tables.

Range("E10").Select
State = ActiveCell.Value
ActiveSheet.PivotTables("PivotTable1").PivotFields("State").CurrentPage = State