Combo Box problem

[zaska]

Hi all,

I have the Formula " =Index (A6:A17,$F$1) " In Cell A2

and a Combo Box with input Range " (A6:A17) " and F1 as the Cell Link.

My problem is the Combo Box is not showing the values in Input Range i.e JAN,FEB,MAR,APR Etc.,

Instead it is showing the number of the Months...1,2,3,4 i.e the values Cell F1


How do i make the Combo Box show the Values " JAN , FEB , MAR " Instead of 1,2,3,4 etc..

I am using this combobox to display chart ....for the months of JAN, FEB etc.,

Kindly suggest the solution

 

[VoG]

If you use an ActiveX combobox from the Controls toolbar and set its ListFillRange it will display the months.

 

[Norie]

That's how forms comboboxes work - they return the index of the selected item not the value to the linked celll.

In this case it might appear to be the month number but that's a coincidence.

How are you using this combobox to display the chart?

If you need the 3-letter month then it might be possible to convert what's returned to the linked cell and use that.

Really depends on how you want to use what's selected from the combobox.

 

[zaska]

Thank you Sir,

How do i use ActiveX combobox ? I have no Idea about it.

I created a simple chart from the values "A2:D1"

But the chart is not changing according to the values selected in Combobox..

 

[VoG]

Excel 2003 and earlier: View > Toobars > Controls Toolbar and insert a combobox.

Excel 2007+: On the Developer tab, Insert and insert an ActiveX combobox.

Right click the combobox > Properties and set the ListFillRange and linked cell.

Click Exit Design Mode.

 

[zaska]

Thank you i understood the problem now..

I am using Excel2003 that might be the reason that the combobox is not showing the values of " A6:A17 ".

 

[VoG]

It isn't because it is Excel 2003.

There are 2 types of comboboxes. One added from the Forms toolbar, the other from the Controls toolbar. You need the one from the Controls toolbar.

 

[zaska]

Sir,

I dragged the Combobox from the Control TollBar and assigned the listfillrange value " A6:A17 " and Link Cell as " F1"

The source data for the Chart is the values in " A2,B2,C2,D2"

A2,B2,C2,D2 Contain the Formula " =Index(A6:A16,$F$1) "

now iam unable to use this combobox for displaying the chart according to the months.

combobox is refereing to the value in Cell F1 which is indexnumber but the not the month names...

A6 - JAN 133 144 155
A7- FEB 189 200 210
A8 - MAR 125 146 169

Thank u

 

[zaska]

Formula to Convert Month Name into number in Cell F1 would Solve my Problem

 

[VoG]

Try

=LOOKUP(F1,{"apr","aug","dec","feb","jan","jul","jun","mar","may","nov","oct","sep"},{4,8,12,2,1,7,6,3,5,11,10,9})