Circular reference problem

[R.Hocking]

Hi all,

I've come up against a problem with referencing between two sheets i
one workbook. The situation is as follows:

On sheet1, I have a complete list of data. This is displayed in
different visual format on Sheet2. I have it set up so far so that, fo
example, if I enter / alter data in cell A1 on Sheet1, Sheet
automatically enters / alters the data in a corresponding cell
However, on some occasions, I may want to alter the data in Sheet2 an
have Sheet1 update the equivalent cell! This seems to cause problems
Does anyone have any suggestions as to a way round this?!

Thanks in advance,

Richard

 

[Bill Martin]

Suggestion #1: Don't do it that way. Only ever modify your data in one place or
eventually you'll shoot yourself in the foot. Particularly as your circular
references become complex extending through multiple cells and you forget
they're there even. Design your workbook in such a way as to not require this.

Suggestion #2: If you're driven to do this, you can enable circular references
with Tools>Options>Calculation. Check the Iteration box. There's a very good
reason not to do this though.

Bill

 

[Bill Martin]

Although you can enable circular references, it's a dangerous thing to do.
Perhaps the better approach is to use VBA to sense when a cell is changed on one
page and copy it's contents to the other. That may appear to be complicated for
you though.

If you're going to muck with VBA anyhow, only have one copy of your sheet and
create two buttons on the sheet - one which will format the whole page one way,
and the second which will reformat it the other. Then you never have to worry
about the data getting out of synch between two pages.

Just turn on the macro recorder, select the data and format it, then turn off
the recorder. Do it again the second way. Now you have the two requisite macros.

Good luck...

Bill

 

[R.Hocking]

Thanks for your thoughts Bill - I know what you're saying about avoiding
circular refs. I think in this case they would be ok, however. The
scenario is that I have on one sheet a list of 810 individual items.
These items are stored in 10 drawers of 9x9. Hence Sheet1 is the whole
list, then I have a further 10 sheets to represent the layout and
contents of each drawer. When an item is removed / altered I would
normally enter this in the Sheet1 list, so all is fine. I just wondered
if, on the odd occasion if I altered it in one of the other sheets, if I
could get Excel to effectively do the reverse and update the Sheet1 ref!
there are no real formulas, just an ='Sheet1'!$E2 type formula in each
cell of sheets 2 to10. Pretty much a glorified filing system really!

 

[Bill Martin]

Like I said, you can accomplish what you want with VBA if you're familiar with
that. You'd have to set it up to monitor when one cell is modified and then
copy the modification to the other sheet.

You'd need to use the Worksheet_Change event. Walkenbach describes it in his
book "Excel 2003 Power Programming with VBA" if you have it, or if a local
library has it. It's on page 624 in my particular edition of the book.

Good luck...

Bill