Multiple Drop Down Box - using the same values when tick box selected

[corkyo4]

Hi all,


I have 4 drop down boxes controlling data on two parts of my worksheet.

When i use the first two boxes i get a result on a graph. The second two drop down boxes do the same but on a different graph.

What i would like to be able to do is use a tick box, so that when the box is ticked, it overrides the second two drop down boxes so that the data matches the first two drop down boxes.

By doing this the user has the oppurtunity to use different data on each graph (when not ticked), or when the tick is clicked, keep the data the same - that way it is quicker to get a result (as you dont need to select 4 boxes, just 2) and it also ensures that the same data is used throughout.

I tried this using an IF function so that when the tickbox is activated it overrides the value in the cell link to the drop down box. This works only once because as soon as you use the drop down box it automatically deletes the IF function and overrides with a number and so its game over.

I cant really think of another way to do this.

Is there a way around this - apologies for not explaining very well.

Thanks

Tom

[Palmetto]

You need to clarify what you mean by drop down boxes. Are these;

• Data Valiation (list) drop downs?
• Combo box from Forms Controls?
• Combo box from Active-X controls?

It makes a difference in providing a correct answer. A sample workbook would be helpful.

[corkyo4]

Hi,

Yeah never thought to specify that. They are drop down boxes from form controls.

The attached sheet should clarify though.

Basically if the tick box is ticked, input 1 should be duplicated in input 2 - so that when changes are made in input 1 it automatically does the same in input 2.

But if the tick box isnt ticked, then input 2 reverts back to its original values and then input 1 and 2 are seperate and must be changes seperately.

Thanks

Tom

[corkyo4]

any takers??

[Palmetto]

You will have to use a helper cell to accomplish this so as not to interfere with the linked cell value when using the drop down.

In your cell of choice use this formula:
=IF($A$13,$A$9,$A$10) - relative to input 2 drop down #1
=IF($A$13,$A$11,$A$12) - relative to input 2 drop down #2

I've used cells J10 and J12 to hold the formula. Put them whereever you want.

Where applicable in your workbook, reference the above formula cells like so:

=IF($A$13,J10,A10)
=IF($$A$13,J12,A12)

If the check box is ticked then the formula returns the value of the cells in column-J, otherwise it useds the values of the linked cell.

See attached.