Block a circular reference so it can change

[Astrid123]

Let’s say following is my situation:

Sheet 1:

	Wifi	Intercom system
Room 1	Yes	Yes
Room 2	No	No

<tbody>
</tbody>

Sheet 2:

	Room 1
Wifi	Yes
Intercom	Yes

<tbody>
</tbody>

Sheet 3:

	Room2
Wifi	No
Intercom	No

<tbody>
</tbody>

I want to be able to make changes in both ways, so either based on the number of the room or based on the value (in this case ‘Wifi’ or ‘Intercom’), and this value is updated in the corresponding alter cell. So what I did is I made a circular reference, let’s say sheet 1!B2=sheet 2!B2 and sheet 2!B2=sheet 2!B1 etc.

So far so good.

The thing is what I’m trying to do is to block the circular reference. If I would change cell B2 in sheet 1 from a Yes to a No, the first time you do this sheet 2 will change as well (because of the circular cell reference). However, if after I would make a change in sheet 2 again, this will not be updated in sheet 1 because cell B2 in sheet 1 changed from a formula towards a value.

Hope I explained well, but what I want is to make a hard cell reference. Anyone any smart suggestions on how to do this?

Cheers!

 

[mole999]

try reducing the iterations in options, so that it dose do what you want (I think)

 

[Astrid123]

try reducing the iterations in options, so that it dose do what you want (I think)

How can I do this?

 

[mole999]

in 2007 go to excel options, select formulas, then calculation options. its not good to actually intend to use circular references (IMHO)

 

[Astrid123]

in 2007 go to excel options, select formulas, then calculation options. its not good to actually intend to use circular references (IMHO)

I get your point, however: any suggestions on how to do this differently?

 

[mole999]

https://www.techrepublic.com/article/get-excel-to-calculate-formulas-with-circular-references/

or you have to go to vba and worksheet change events

 

[Astrid123]

https://www.techrepublic.com/article/get-excel-to-calculate-formulas-with-circular-references/

or you have to go to vba and worksheet change events

Do you have a suggestion for any VBA or worksheet change?