Using mach if or vlookup in excel

[southwoman]

Hello, am having some issues representing some regional data in my charts. I have 4 regions with each having 3 - 5 sub regions and relevant data. What I am seeking to do is create a drop down list that matches the regions to its sub region with the data set included.


For example:


Region 1

SBR1, 456, 800

SBR1a, 234,400

SBR1b; 200, 300


Region 2 has

SBR2, 900,600

SBR2a, 350, 250

SBR2b 256, 890


Region 3 has

SBR3, 987, 654

SBR3a, 345, 780

SBR3b 300, 500


How can i do a drop down list that only includes and SHOWS the subregion and its data for that particular region only.


help anyone?

 

[fred]

you will probably need 2 drop down list, if i understand you correctly.


see this for idea...http://chandoo.org/forums/topic/dependent-drop-down-lists

 

[bobhc]

Good day southwoman

I use drop drop validation list a lot, mainly to make it easier for the persons using the spreadsheet to input the correct data and NOT what they think should be input, and if there is one thing I have learned, it is that unless you hold their hands, guide them, cajole them, threaten them,that they will enter any data other that that data that you have told then to enter. I learned much from this site and one other that has a very easy to follow instructions in drop down dependent data validation I am sure it will show you what you need. http://www.contextures.com/xldataval02.html

 

[southwoman]

Hi thank you for this. I can see myself really using this site. I have a rethink of the spreadsheet needs and a drop down box is not going to work here, unfortunately


What I would definitely need is for


Cell A1 to contain the Region name and cells A2, A3, A4 etc to contain the corresponding sub regions ignoring totally any other sub regions?


Do you know how this can be done without an extremely long If statement?


Regards

 

[Faseeh]

Hi Southwoman,


I have assumed that your data is present in between G1:I4 like this:

[pre]

Region 1	        Region 2	    Region 3
SBR1, 456, 800	        SBR2, 900,600	    SBR3, 987, 654
SBR1a, 234,400	        SBR2a, 350, 250	    SBR3a, 345, 780
SBR1b; 200, 300	        SBR2b 256, 890	    SBR3b 300, 500[/pre]
..in Cell A1 in present your "Data Validation" list containing Region1~3, add this formula into Cell A2 & Drag down to A4:


=OFFSET($F$1,ROW(A1),MATCH($A$1,$G$1:$I$1))

Hope that it helps.


Regards,

Faseeh