Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Circular reference? (Excel 2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Circular reference? (Excel 2000)

    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.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Circular reference? (Excel 2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Circular reference? (Excel 2000)

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

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Circular reference? (Excel 2000)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Circular reference? (Excel 2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •