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.

 

[pgc01]

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

If you don't take it out of the IF the code will only work 1 time if you change for ex. A1

Let's say you change A1

Before changing A1 you set .EnableEvents to False,

then you execute the first branch of the IF and leave the IF without changing .EnableEvents to back True

Since the assignment of .EnableEvents to True is in the third branch of the IF it will only be executed when you change D3.

With .EnableEvents set to False the events will not trigger the corresponding event procedures and the code will not execute again.

 

[footoo]

Yes, of course. I hadn't noticed it was in the wrong place. Thanks.

 

[pgc01]

... 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).

Not that it's important, but you can change the values of several cells at a time with a simple assignment, like:

Range("A1:E1").Value = Array(1, 2, 3, 4, 5)

In this case the event procedure is fired just once.
With your code E1 would get the value of A1, because A1 is the first one in the IF's

As I said, not important, just a remark.

 

[footoo]

Not that it's important, but you can change the values of several cells at a time with a simple assignment, like:

Range("A1:E1").Value = Array(1, 2, 3, 4, 5)

In this case the event procedure is fired just once.
With your code E1 would get the value of A1, because A1 is the first one in the IF's

As I said, not important, just a remark.

But that's just one of the ways of writing : copy/paste(values only).