Block a circular reference so it can change

Astrid123

New Member
Joined
Sep 25, 2017
Messages
16
Let’s say following is my situation:

Sheet 1:
WifiIntercom system
Room 1YesYes
Room 2NoNo

<tbody>
</tbody>

Sheet 2:
Room 1
WifiYes
IntercomYes

<tbody>
</tbody>

Sheet 3:
Room2
WifiNo
IntercomNo

<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!
 
Last edited:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
try reducing the iterations in options, so that it dose do what you want (I think)
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
in 2007 go to excel options, select formulas, then calculation options. its not good to actually intend to use circular references (IMHO)
 

Astrid123

New Member
Joined
Sep 25, 2017
Messages
16

ADVERTISEMENT

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?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,557
Messages
5,637,040
Members
416,955
Latest member
Gohar hussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top