Excel Combo Box Problem

[mispriss04]

I created a form using the control toolbox and made combo boxes with true or false as the selections. On the master sheet that the form is being linked to the responses are showing as numbers rather than the actual text selection. Is there a way to fix that?

Also is there a way to determine a variance per question? Such as having 10 forms all feed to one sheet (which i have already done) and then have it tell you the variance of people chosing different answers? I basically need to see if people agree and if not how far off they are.

 

[Andrew Poulsom]

Welcome to the Board.

You will get the actual value if you use ComboBoxes from the Control Toolbox. For those from the Forms Toolbar you will get the index number, which you can use in a formula with INDEX on the ComboBox's ListFillRange.

http://www.jkp-ads.com/Articles/controls06.asp

 

[mispriss04]

Thanks!

Ok, so I've already created about 11 forms...all using ComboBoxes from the Forms Toolbar (basically, I'm not going to go back and redo anything with the Control Toolbox). I'm not exactly sure how to use the fomula with the index number for the ListFillRange.

I have 11 forms (same form, just duplicated) all feeding to a master sheet. The input range is being taken from a sheet ListBoxData to populate the available drop downs. And then I have a reponses sheet that the cell is linked to. So I'm not sure on how to fix the problem?

 

[Andrew Poulsom]

There's an example of a formula in the link I posted:

=INDEX(ListForComboAndList,C1)

The first argument is the range used as the ListFillRange, and the second argument is the linked cell.

 

[mispriss04]

where do i put the formula? lol sorry im a newbie

 

[mispriss04]

in the format control box it says:

Input range: ListBoxData!$A$1:$A$2
Cell link: Responses!L6
Drop down lines: 2

 

[Andrew Poulsom]

So in a spare cell on sheet Responses, eg M6 put:

=INDEX(ListBoxData!$A$1:$A$2,L6)

 

[mispriss04]

oh ok! I got that....cool...thank you!

So will I have to do that repeatedly to have all the responses to show up as text?

 

[Andrew Poulsom]

Yes, you will need to do it for each of your linked cells.

 

[mispriss04]

LOL...thats going to take a while....

Thank you so much for your help!!