[SOLVED] Permanently Link Cells of Multiple Sheets

I have two different sheets in excel. Those sheets contain information that I would like to sync between the sheets. However, they also contain data that I do not want synced between both sheets. What I want is to be able to edit the synced data from either of the sheets, and have it change on both sheets. But there are only a few cells that I want to be able to do this from. The other cells I want to be different between sheets.

The only way I know how I could do this is to select the sheets simultaneously with ctrl and edit the desired cell this is not desirable because I would have to go back and forth between having them both selected, and individually.

Is there a way to do what I am looking for?

Alternatively, if there is a way I could do the reverse, and instead keep all my data on one single sheet, and just click a button to switch between the two sets of data; i.e. I press a button and cell A6 and D5 switch to their other set of values, and I can press that button again to switch them back, then that would work too.

Thank you for reading, and I hope you can help!

This can be achieved with a combination of vlookup and conditional formatting without the need to link any cells.
Everything is the one sheet but it all applies equally if you put the conditional formatting into another sheet

How do the values disappear?
- they do not - they are conditionally formatted to a white font (= background colour) if the condition is met

How is Night or Day determined?
- B11 holds current time which is determined by this formula:

Formula:

Please Login or Register  to view this content.

- the formula in B12 looks up that value in the table below to get a Night or Day result

Formula:

Please Login or Register  to view this content.

Time in Excel is a value between 0 and 1 and for vlookup purposes A15 = 0 , A18 = 1 (both formatted as time), the other 2 values are derived by formula (based on values In B8 to D9)

Formula:

Please Login or Register  to view this content.

How to set Day and Night time ranges
- using dropdowns in B8:D9

when it is "Day"

Day.jpg

when it is "Night"

Night.jpg

If you have a problem adapting this to your needs, fire a question back

You are welcome
You are allowed overlapping cells with conditional formatting, and
- your conditions are mutually exclusive
- so you could have the 2 conditional formats apply at the same time over the same range and get the correct result (more by fluke than design!)
OR
you could have a single condition that meets both cases
IF "I am a nighttime animal" AND time = "Day" THEN my font is white OR IF "I am a daytime animal" AND time = "Night" THEN my font is white

=IFS allows multiple conditions (not available in earlier versions of EXcel)
OR
a combination of =IF and =AND and =OR

Here is a single column solution

ConditionalFormat02.jpg