Index formula and combo box problems

Cossie

Active Member
Joined
May 6, 2002
Messages
327
I have a spreadsheet with the following data<Pre> week1 week2 week3 week4
Australia 20.2 20.4 20.4 20.5
New Zealnad 18.6 21.5 18.6 25.1
Aust Ave 19.5 19.7 19.9 19.8
NZ Average 20.5 25.5 21.5 20.5</pre>

I want to use the index formula so that i could graph the data using a combobox.
I am using this formula "=index(b2:b5,a5)". My combo box is attached to a list of places, but when i run the combo box it returns the wrong line of data because of all the averages (lines 3 & 4 etc). How can i eliminate this problem so that it only reads the place data. Thanks
This message was edited by Juan Pablo G. on 2002-10-14 22:18
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Alriemer

Board Regular
Joined
Aug 18, 2002
Messages
102
Cossie,

Here's a suggestion, but it's cumbersome and maybe someone will have a more elegant idea.

1) Use IF and SEARCH to isolate only the place names in a separate column by searching for " Avg", returning "" if the record fails.

2) Use Chip Pearson's array formula to consolidate the place names:
http://www.cpearson.com/excel/noblanks.htm

3) Create a dynamic named range to capture only the used rows here and use that as the source for your combo box. (I'm pretty sure you can use named ranges in combo boxes.

Hope this helps.
 

Alriemer

Board Regular
Joined
Aug 18, 2002
Messages
102
Cossie,

I think I misunderstood the problem. What about using INDEX to return the actual place name from the source of the combo box, and then using MATCH to find the position of that in a2:a5?

Hope this helps...
Alriemer
 

Cossie

Active Member
Joined
May 6, 2002
Messages
327
Thanks for the advice are you able to give me example of what the formula would look like??? Tks
 

Alriemer

Board Regular
Joined
Aug 18, 2002
Messages
102

ADVERTISEMENT

Cossie,

Something like this:
First to return the place name you picked from the combo box: =INDEX(Place_Name_List,Combo_Box_Result)

Then to find the position of that place name in your data table and pull the appropriate data: =INDEX(B2:B#,MATCH(Place_Name,A2:A#)).

Thus if "Japan" was the third choice on the combo box and appeared in the fifth row of the data table (A6):
Choose Japan from combo box returns 3
INDEX(Place_Names_List,3) returns Japan
MATCH(Japan,A2:A#) returns 5
INDEX(B2:B#,5) returns data.

Of course you could combine the formulas one step further: =INDEX(MATCH(INDEX()))
Hope this helps. Maybe someone can suggest a less roundabout way.

Alriemer
 

Cossie

Active Member
Joined
May 6, 2002
Messages
327
Thanks for the help ~ I am stuck on the first bit regarding returning the value of the combo box . Can u help me some more please I would really appreciate it.
 

Alriemer

Board Regular
Joined
Aug 18, 2002
Messages
102

ADVERTISEMENT

Cossie,

Sorry not responding sooner. Here is an example: let A1:A3 hold the set {Russia,France,Germany}. Create a combo box using the forms toolbar, selecting A1:A3 as the input range and B1 as the cell link. Then pick "France" from the combo box. The value 2 appears in B1.

In C1, enter this formula:
=INDEX($A$1:$A$3,$B$1)
This returns France, the second value in the input range.

Suppose A10:A14 contained the set {France,Germany,Western Average,Russia,Eastern Average} and C10:C14 contained the data set {100,200,150,400,400}.

In order to pull out the data point for the place picked on the combo box, here is one approach:
=INDEX($C$10:$C$14,MATCH($C$1,$A$10:$A$14,0))
This will find France in the range A10:A14, see that it is the first value, and return the corresponding data point from C10:C14.

I hope this helps you sort things out. I keep hoping someone will propose a less roundabout way.

Alriemer
 

Cossie

Active Member
Joined
May 6, 2002
Messages
327
Thanks al for the response I will try it and let you know. Cheers
 

Cossie

Active Member
Joined
May 6, 2002
Messages
327
Thanks al that worked a treat. I failed to think ahead and now have another problem. If i take your example of russia, germany and france and say these are grouped by continent eg europe, i can insert a column and put in europe where appropiate and do the same formula as you have advised, but i need to be able to select Europe from the combo box, but graph the three individual countries totals. i can get two to work by playing with the "0" at the end of the formula, but i cant seem to get a third line to work. Any help is greatly appreciated.
 

Alriemer

Board Regular
Joined
Aug 18, 2002
Messages
102
Cossie,

Not sure exactly what you're trying to do - if I'm off base here it would help to have more detail about your data.

If you want to create a graph from your data that skips the subtotal lines like europe, you could create a dummy data table where every cell is linked to the real table except the records you want to skip. Fill those in with =NA(). This should make Excel skip those data points. In practice I sometimes have trouble with this. Another approach would be to group and hide the rows you don't want to graph (Data>Group and Outline>Group). The graph will only show the rows that are visible. Does this address your question?

Cheers
 

Forum statistics

Threads
1,148,171
Messages
5,745,173
Members
423,931
Latest member
thangvan114

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top