Index formula and combo box problems

[Cossie]

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

 

[Alriemer]

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]

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]

Thanks for the advice are you able to give me example of what the formula would look like??? Tks

 

[Alriemer]

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]

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]

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]

Thanks al for the response I will try it and let you know. Cheers

 

[Cossie]

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]

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