Multiple locations of an input cell

[DanielThorp]

Is there a way to establish a "party line" of cells, all connected with formulae that cannot be over-written?

In other words, is there a way to establish a circular reference of formulae that cannot be broken by changing one of the cells in the circular reference from a formula to a value?

In other words, for example, can I create a circular reference of A1=D3=E1=A1=D3=E1=A1=D3=E1=A1=D3=E1=A1=D3=E1=A1=D3..., and then enter "$15" in cell A1 thus causing the value in D3 and F5 to be $15 without overwriting A1's formula (I want A1, D3, and E1 to retain their formulas, while still being able to enter a value into A1, D3, and/or E1).

In other words, follow these steps to see what I'm trying to ask:
1. Open a blank workbook.
2. In A1, enter "=D3".
3. In D3, enter "=E1".
4. In E1, enter "=A1". Notice that you have now created a 3-way circular reference, and the resulting value in these three cells is 0.
5. Now, how do I insert "$15" into A1, D3, and/or E1 without overwriting their formulae? I want A1, D3, and E1 to all show "$15" as a value, but I still want them to retain their formulas that we established in #2, #3, & #4.

I do know of a way to achieve my desired result, but it gives me a headache: Create a worksheet titled "INPUTS" within my workbook, and have this "INPUTS" sheet house all of the my data inputs that affect calculations on the other sheets, and then use the "View Side By Side" view to constantly keep the "INPUTS" sheet at my fingertips as I go through other sheets to see how my data inputs affect other sheets in real time. But I HATE using the "View Side By Side" view, so I'd rather be able to have a "party line" input cell(s) appear on multiple pages whilst I change its value and check the resulting affect on other sheets in real time.

Thanks in advance for any help! Let me know you need more clarification as to what I'm asking.

 

[footoo]

What about using a Worksheet_Change procedure :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Not Intersect(Target, [A1,E1,D3]) Is Nothing Then
    Application.EnableEvents = False
    Target.Copy [A1,E1,D3]
    Application.EnableEvents = True
End If
End Sub

 

[footoo]

This is better :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Not Intersect(Target, [A1,E1,D3]) Is Nothing Then
    Set rng = [A1,E1,D3]
    Application.EnableEvents = False
    If Not Intersect(Target, [A1]) Is Nothing Then
        [A1].Copy rng
    ElseIf Not Intersect(Target, [E1]) Is Nothing Then
        [E1].Copy rng
    ElseIf Not Intersect(Target, [D3]) Is Nothing Then
        [D3].Copy rng
    Application.EnableEvents = True
    End If
End If
End Sub

 

[DanielThorp]

Thank you for the reply, footoo! I'll try out the second code you posted.

 

[pgc01]

Hi footoo

Just curious: why is the second code better?

 

[footoo]

Hi footoo

Just curious: why is the second code better?

The first code only works if a single cell is changed.
The second code works if a single cell is changed and if more than one cell is changed (and intersects [A1,E1,D3])

 

[pgc01]

The first code only works if a single cell is changed.
The second code works if a single cell is changed and if more than one cell is changed (and intersects [A1,E1,D3])

Yes, that's why I would prefer the first code.

Since the goal is to write in one of the cells and have its value be written also in the other 2 I don't see the point in changing more than 1 at the same time.

For ex., if you write 3 in A1 and 5 in E1, your code will choose the value in A1 to copy to E1 and D3, and will ignore the value you wrote in E1, just because of the order of the If's in your code.

I find this a bit confusing, your first code would be my choice, but both work and have their own logic so it's a question of choice.

 

[footoo]

Yes, that's why I would prefer the first code.

Since the goal is to write in one of the cells and have its value be written also in the other 2 I don't see the point in changing more than 1 at the same time.

For ex., if you write 3 in A1 and 5 in E1, your code will choose the value in A1 to copy to E1 and D3, and will ignore the value you wrote in E1, just because of the order of the If's in your code.

I find this a bit confusing, your first code would be my choice, but both work and have their own logic so it's a question of choice.

It's really up to the user as to which is appropriate.

For example, if a value in D2 is dragged through D3, the first code doesn't work but the second code does.

In your example, the only way that A1 and E1 could be changed at the same time is by copy/paste or by dragging values.
In this case the first code would not work and the second code would work as you have explained.

It's all really a question of what the user wants.

 

[pgc01]

It's really up to the user as to which is appropriate.
...
It's all really a question of what the user wants.

I agree

In your example, the only way that A1 and E1 could be changed at the same time is by copy/paste or by dragging values.

Just to be thorough, ... or manual assignment or changed by code


Also, I think that in that case you must take the .EnableEvents out of the IF:

'        Application.EnableEvents = True ''''''''' take it out of the IF
    End If
    Application.EnableEvents = True

 

[footoo]

Just to be thorough, ... or manual assignment or changed by code

But manually assigning to each cell (to put 3 in A1 and 5 in E1) would trigger the change procedure for each cell, so does not negate the second code.
Similarly, changes by code to more than one cell, without triggering the change procedure for each cell change, could only be done (I think) by copy/paste or filling a series(dragging).

Also, I think that in that case you must take the .EnableEvents out of the IF:

'        Application.EnableEvents = True ''''''''' take it out of the IF
    End If
    Application.EnableEvents = True

Why do you suggest that it should be taken out of the IF?