I normal use a drop down arrow in data validation. The problem with drop down cell is you have to hover over the cell to see the arrow. Combo box appears to be much better and visible. Having created a combo box, I am, however, unable to link it to cell which has a formula. For e.g., in Column A, I have a drop down lists, YES, & NO. In a data validation, I can create a drop down in Column A. In column B, I can create a formula which is linked to Column A, YES and NO. This works fine with data validation, but if I do the same with Combo box, it does not. I do not know what is missing from the combo box properties, or set-up, or is it that you cannot link a combo box to another cell which has formula?
Any help would be much appreciated.
Replies (9)
Please login or register to join the discussion.
Some thoughts
My initial reaction is Data validation is the way to go if at all possible.
a) Is your desire to avoid using Data validation purely because of the display? An approach here could be to colour code the cells.
b) If you are going to use a Combo box, is this one instance or many? If the latter, I think you are going to run into problems copying without the use of VBA
c) Are you using the ActiveX or Forms version of the Combo box? If the latter, it will return a value based on the drop down choice made into a cell of your choice. Right click the combo box, go into Format control, choose the control tab and specify the output cell in "Cell link". This output can then be used to drive your formulae.
I don't really understand
Evening
I am more than happy to try to help. If you would like to send something to: elliott @ recenseo.co.uk (with all sensitive data removed) I will see what I can do.
Regards
Elliott
A combo box doesn't seem likely to do what you want
It gives a list of items for you to select one and then returns the position of the item selected in the list in the cell link set to take the answer. So with YES or NO as your options it will return 1 or 2 respectively and you will need the formula in column B to check for that rather tha YES or NO
If all you really want is Data Validation but with it visible is seems a bit of a fag for have to create numerous combo boxes, one for each row/cell
If it is just a Yes/No answer you want then CheckBoxes, or possibly Radio buttons, are probably a better bet, with CheckBox slightly easier to implement than Radio Button, but both still a bit of a fag to set up
2 types of dropdown
I agree Data validation is useful but the lack of an arrow makes it less intuitive compared to a Combo Box.
There are 2 types of Combo Box in Excel, one is an ActiveX control and the other is a more basic Form control. I tend to use the ActiveX because it has much more flexibility but it is more complex.
However, both types return a value to a cell and therefore the cell can only contain a value (numeric or text) but not a formula. This cell can then be used in other cells which can contain a formula.
The Form Combo returns an index number of the selected item in the list; so if you have a list of items (e.g. YES & NO) and you select the NO item, the linked cell will have the vaue of 2.
An ActiveX Combo can be set up in the same way or, if required, it can be configured so that the actual item text in the list is returned to the cell, so in the example above if NO is selected the text NO will appear in the linked cell rather than the number 2.
I can email an example to you if that would help.
Andrew
I suspect
that your post may have overlapped with Andrew's but, at the risk of duplicating his answer, the Input Range is indeed the range where "Yes", "No", etc. is held. The Cell link is where the result of the user's choice is output - the result is the item number from the input range. So, if your input range has "Yes", "No", when the user chooses "Yes", the value of 1 will be returned to the Cell link and the value 2 if "No" is chosen (this is using the simpler Form control).
You can then use this value in formulae as a substitute for "Yes" or "No" or to return "Yes"/"No" e.g. =If(A1=1,"Yes","No").
Does that help?
Your problem is still going to be
if you have 175 cells in column A where you want the answer YES or NO that you would have done with Data Validation, then to replicate the Data Validation dropdown using any of ComboBox, CheckBox or Radio option you are going to have to add 175 seperate items and assign Cell Link for each.
It sounds as if you are just creating a huge amount of work to achieve a slightly different version of Data Validation
I'd discovered something about dropdown in combobox control, hope this can be useful to your situation.