Combo Box could not be linked to a formula

Combo Box could not be linked to a formula

Didn't find your answer?

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.

avatar
By paulwakefield1
07th Sep 2011 17:38

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.

Thanks (0)
Replying to petersaxton:
avatar
By Cantona1
08th Sep 2011 11:40

COMBO BOX

Thank you all for your comments!

Paul: I have to admit that I am struggling to understand how the combo right click menu works: In the Form Control page, there are two Buttons: My understanding is that the "Input Range" is picked from the list, ie Yes, and No. I do not know what  is "Cell Link". When I clik the cell, I can see the Yes and No from the drop down arrow, but it is not linked to my next column formula. I also tried to link the list of Yes and No to the "Cell Link", and when I do this, it returns numbers 0 and 1. I am sure I do not know how how the combo box works.

In Column "A" I have the combo box with  lists of Yes and No,and in Column "B", I have a formula like if the answer is "YES", multiply by X amount, if "NO,, it should return a zero value. In data validation, this is simple. When I click the yes arrow from Column "A", Colmumn "B" returns a value. All I have to do is link the Yes and No lists in Column "A".

 

Thanks (0)
avatar
By ElliottRoss
07th Sep 2011 19:12

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

www.recenseo.co.uk

Thanks (0)
avatar
By ACDWebb
08th Sep 2011 09:27

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

Thanks (0)
avatar
By AndrewOrchard
08th Sep 2011 11:35

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

www.quarryview.com

 

Thanks (0)
avatar
By paulwakefield1
08th Sep 2011 12:28

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?

Thanks (0)
avatar
By ACDWebb
08th Sep 2011 14:12

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

Thanks (0)
avatar
By Cantona1
08th Sep 2011 15:33

COMBO BOX

Andrew, Paul and ACDWebbl:

 

Thanks for your comment!

Paul, It does help. Data validation and boxes do not work the same way.

On a cambo, I have also need to creat two separate columns,unlike data  validation. The arrow in Combo looks more visable than data validation.

I now understand that you can't link a combo directly to a formula. It only takes numeric value. As ACD said this would be fine if the list is not that big.

Thanks (0)
avatar
By xiaoyuandlg
25th Mar 2014 06:28

I'd discovered something about dropdown in combobox control, hope this can be useful to your situation.

Thanks (0)