CODEWITHSUNDEEP
excelfunctionvlookup
JohnathanBrown
JohnathanBrown

Reputation: 1

Excel: Different Cells with Duplicate Function

I have tried finding an answer to this question but I'm not sure how to even phrase it.

Say I have three cells A1, A2 and A3:

I want A2 to mirror and provide an identical function to A1, but I also want both to be dependent on each other. For clarity, if I select 'X' on A1, A2 will also display 'X'. If I change 'X' to 'Y' on A2, A1 will then change to 'Y'. Either way, I want A3 to display the appropriate result whether or not the function is changed on A1 or A2.

Is this achievable? Or am I just messing with circular references?

Upvotes: 0

Views: 87

Answers (2)

user6569321
user6569321

Reputation:

It is possible to connect two cells without VBA OR circular references, but it requires the use of form controls. Please reference this example for now.

enter image description here

Comboboxes

Comoboxes can be used as dropdown boxes (just like your data validation) but require two things: 1) an input range containing your list and 2) a linked cell. The linked cell returns an integer equal to the position of the selected value in your input range. Select X1 in your combobox and the the linked cell becomes 1, select Y1 and it becomes 2, etc.

To make your comboboxes, go to Developer in the Ribbon and select Insert > Combobox (form control). Put two boxes over cells A1 and B1, or wherever you want your lists to be. Right click each combobox and select "Format Control . . ." Choose your linked cell (you can put it under the combobox to easily hide it) and make an input range with your {X,Y,Z}. Make each combobox EXACTLY the same.

Now here's the important part: linked cells and their comboboxes are a two way street. Select Y1 in your combobox and the linked cell becomes 2; change your linked cell to 3 and the combobox becomes Z1. Therefore, when the first combobox changes the linked cell, the linked cell changes the second combobox. No circular reference, no VBA.

Formula for identical comboboxes

In cell C1 enter the formula

=VLOOKUP(INDEX($G$2:$G$4,$E$1),$J$2:$K$4,2,0)

It's your standard VLOOKUP, but since your linked cell returns an integer we have to convert it back into a lookup value.

INDEX($G$2:$G$4,$E$1)

INDEX uses the same list you used for you comboboxes and $E$E1 is the position in that list of the thing you selected. Therefore, it outputs what you selected in the combobox.

And voila! You're done.

But what if I want connected lists with separate values?

Please reference this example:

enter image description here

In this example, both connected comboboxes (same linked cell) have two separate lists: {X1,Y1,Z1} and {X2,Y2,Z2}. To accomplish this, simply have each combobox reference a different input range. The first box references $G2$G4 and the second $H2$H4. Again, when you change one combobox, the other will change: select X1 and the second combobox becomes X2, etc.

Formula for different comboboxes

Since the linked cell is the same for both comboboxes, your formula can't tell which list it should reference. You can use a simple data validation (or combobox!) list with {1,2} where 1 represents the first combobox and 2 the second. In this example its cell $A$4. Then you can use the formula:

=VLOOKUP(IF($A$4=1,INDEX($G$2:$G$4,$E$1),INDEX($H$2:$H$4,$E$1)),$J$2:$K$7,2,0)

It's the same formula we used for identical comboboxes, but this time we're choosing which list to get our lookup value from based on which combobox is selected in cell $A$4. If you wanted your VLOOKUP to have different table arrays, a simple two VLOOKUP formula:

=IF($A$4=1, VLOOKUP(INDEX($G$2:$G$4,$E$1),MyRange1,2,0),VLOOKUP(INDEX($H$2:$H$4,$E$1),MyRange2,2,0)

will suffice.

You can also use list boxes or ActiveX comboboxes instead of Form control comboboxes. The former will give you an actual list but it takes up space and you don't get that similar effect to data validation drop-down boxes. ActiveX controls are simply too complicated and resource heavy for what we're trying to do here.

I noticed too late that I used different ranges in your example, but simply move the comboboxes and change the formulas to wherever you'd like.

Upvotes: 2

Dirk Reichel
Dirk Reichel

Reputation: 7979

To have 2 cells always showing the same value regardless which one you input something, you need VBA. Something like:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target.Parent.Range("A1"), Target) Is Nothing Then
    If Target.Parent.Range("A1").Value <> Target.Parent.Range("A2").Value Then
      Target.Parent.Range("A2").Value = Target.Parent.Range("A1").Value
    End If
  ElseIf Not Intersect(Target.Parent.Range("A2"), Target) Is Nothing Then
    If Target.Parent.Range("A1").Value <> Target.Parent.Range("A2").Value Then
      Target.Parent.Range("A1").Value = Target.Parent.Range("A2").Value
    End If
  End If
End Sub

in your worksheet code part.

With just formulas, that is not possible. Also keep in mind, that you need the same data validation for both cells or you may get in trouble.

For A3 just pick one of the cells for your formula (as both show the same value it doesn't matter which one).

Upvotes: 0

Related Questions