I have two tabs. The one tab contains a complex model ("Model"), and the
other is a control or summary ("Control") of key values and settings in the
model.

I want to be able to change the key values in either the "Model" tab, or
"Control" tab. If I have the Model tab refer to the Control tab for these
values, it gets really annoying to trace formulas. If I have the Control tab
refer to values in the Model tab, it is annoying to have to go to the Model
tab to change the value. I want to be able to look at the Control tab and be
able to change values, and I want to be able to look at the model tab and
change the same values. By definition I want the cells on the two sheets to
behave as if they are the same cell.

Please help!!!

If I can offer an opinion: to relieve all the annoyance, would it be an
idea to have your "Control" tab contain the master information, and
have the "Model" tab refer to the Control tab for key data points.
That way the key data is found in one and only one place. If you have
to change the key data points frequently, you can have two instances of
the spreadsheet showing the Control tab and the Model tab onscreen
simultaneously.

I've found this works for me. Pontification ends here!

Thanks for the try Dave,

Nope, this is what I'm currently doing, but auditing formulas is a
nightmare. If I want to trace a complex formula somewhere in the model, I'm
forced to switch between the model sheet and control sheet.

Cheers,

Johan

"Dave O" wrote:

If I can offer an opinion: to relieve all the annoyance, would it be an
idea to have your "Control" tab contain the master information, and
have the "Model" tab refer to the Control tab for key data points.
That way the key data is found in one and only one place. If you have
to change the key data points frequently, you can have two instances of
the spreadsheet showing the Control tab and the Model tab onscreen
simultaneously.

I've found this works for me. Pontification ends here!

Maybe you could just have a portion of the model worksheet retrieve the values
from the control worksheet.

=control!a1
=control!a2
etc

Then in use these "echo-only" cells (on the Model worksheet) in your formulas on
that same Model worksheet.

If you only have a few, you could put them in the top few rows and then use
Windows|freeze panes to make sure those rows are always visible.

Alternatively, you could use an event macro that updates the opposite sheet's
information when a change is made to the active sheet.

But personally, I wouldn't use this. Lots can go wrong and you could be out of
sync really fast.



Johan wrote:

Thanks for the try Dave,

Nope, this is what I'm currently doing, but auditing formulas is a
nightmare. If I want to trace a complex formula somewhere in the model, I'm
forced to switch between the model sheet and control sheet.

Cheers,

Johan

"Dave O" wrote:

If I can offer an opinion: to relieve all the annoyance, would it be an
idea to have your "Control" tab contain the master information, and
have the "Model" tab refer to the Control tab for key data points.
That way the key data is found in one and only one place. If you have
to change the key data points frequently, you can have two instances of
the spreadsheet showing the Control tab and the Model tab onscreen
simultaneously.

I've found this works for me. Pontification ends here!



--

Dave Peterson