Circular reference? (Excel 2000)

[neil]

I have a multi-worksheet model, and I'm getting some wierd results which I think may be caused by a circular reference.

In broad terms this is what (the relevant bit of) my model does:-

I import a list values into column a in sheet 1.
I manually enter adjustments into columns b-e. Column f is a subtotal.
Sheet 2 copies the values from column a in sheet 1, and then aportions them according to pre-defined criteria formula in sheet 2. The apportioned values are listed in a later column in sheet 2.

Back in sheet 1, column g pulls in the apportioned values column from sheet 2.

Column h is a futher sub-total, which is then used to populate another sheet elsewhere in my model.

The strange values seem to be related to the rows which include an apportioned figure from sheet 2.

This sounds like a circular reference issue, but I am no expert on such things.

Can anyone tell me if my logic is flawed, or if I have done something which I must avoid? And if so, how I might avoid it.

And can anyone point me at any articles on the web dealing with such scenarios in multi-sheet models, what you mustn't do, and how to avoid it?

Many thanks in anticipation.

Neil

[HansV]

Do you see Circular: A1 or something like that in the status bar?

It would be helpful if you could attach a scaled down version of your workbook.

[neil]

I haven' t seen a Circular:A1 (or similiar) message.

I will try to post a scaled down version later.

In the meantime, can anyone confirm if there is an inherent problem in setting up a multi sheet model like this:-

Sheet 1 has (say) 20 columns of data.

An external dataset is imported into column 1.

Calculations are performed which result in a subtotal in column 10.

Sheet 2 picks up the results from column 10 of sheet 1.

Calculations are performed in Sheet 2 which result in a subtotal in column 5 (of sheet 2).

Back in Sheet 1, that subtotal (from column 5 of sheet 2) is picked up in column 11, and more calculation performed on it, giving a final subtotal in column 20.

That (column 20) subtotal is picked up by Sheet 3, which adds it to other figures, to give another subtotal.

Sheet 4 picks up that final subtotal from sheet 3 - only it is the wrong value!

Help!!

Neil

[HansV]

I'm afraid it's impossible to tell without seeing the actual calculations.

[neil]

Hans

I spent about 40 minutes creating a pared down version of my model, only to find at the end that the errors had miraculously vanished! And I do have auto-calculate enabled!!

But I am still a bit worried by what I saw earlier....

Can anyone put my mind at rest as to whether there is some fundamental flaw in a multi sheet model which works something like this:-

Import data into column A, sheet 1.

That sheet then performs various calculations / adjustments, to arrive at a subtotal in, say, column F.

Sheet 2, column A then picks up the subtotal from sheet 1, column F.

Sheet 2 performs various calculations / adjustments, to arrive at a subtotal in, say, column D.

Sheet 1 then picks up the subtotal from column D in sheet 2, and places those values in column G (sheet1).

The rest of Sheet 1 performs various calculations / adjustments to those column G values, arriving at a final value in, say, column X.


In other words, the flow of data is into sheet 1, out of sheet 1 and into sheet 2, and then back into sheet 1, though only into a part of sheet 1 not previously 'used'.


Also can anyone point me at any advice on things to avoid when creating mult-sheet models, and how to audit them?

Thanks for your help, y'all.

Neil

[HansV]

What you describe shouldn't be a problem, in itself. There is no objection to calculations flowing back and forth between sheets.

You could try reducing your workbook step by step, testing at each stage. Perhaps you can find the crucial step.