Circular Calculation

**Taylor_1978** · 09-28-2007, 10:16 PM

Hi,

I'm having a problem with a circular calculation and hoping someone can help. I run a sports business and I am trying to create a Duty Report system where each day it lists who has played and what game fees they owe. Generally this would be $45 plus any overdue fees.

I have uploaded the following 3 files for you to look at:

http://citysidesports.com/excel1.jpg

http://citysidesports.com/excel2.jpg

http://citysidesports.com/excel3.jpg

In excel1 you will see the Duty Report for the 1st of September. Under the heading Game Fees I have entered number 1 for TID. This then looks up team number 1 in the Data Book (excel2) and places the team's name in the next column.

In the following column it shows the amount the team is required to pay. It does this by looking up the team's usual game fee (excel2) and adding to it any overdue amounts (excel3). The overdue amount needs to include any fees from last month (excel3 - Column C) plus overdue fees added from the current month (excel3 - Column D).

This is where my problem occurs. Because the excel3 - Column D amount comes with the following formula: =SUMIF('1st'!A$6:A$33,Previous!A3,'1st'!J$6:J$33)+SUMIF('2nd'!A$6:A$33,Previous!A3,'2nd'!J$6:J$33)+C3

At the moment that only adds up the amounts from the 1st and 2nd, but eventually will go all the way to the 31st.

In excel1 Column J, it uses the formula: =IF(A6<>"",F6-G6-H6,""). However it won't calculate obviously because of the circular reference, because it is linked with excel3 Column D.

I hope I've explained myself correctly. Probably not!

If anyone understands what I'm trying to achieve and knows how I can combat this your help would be appreciated!

Or please, ask me questions so I can explain clearer!

Thanks in advance!

Taylor.

**Taylor_1978** · 10-01-2007, 01:34 AM

Bump! Sorry, message was getting a bit low, and quite urgently need help on this.

Thanks in advance!

**Taylor_1978** · 10-02-2007, 10:35 PM

Okay - I've added a zip file with the two excel files in question. Maybe this will help me in getting some help with this.

The file 'Data' Worksheet 'Teams' shows a list of all teams and the amount due per game played (highlighted in yellow).

The file 'September2007' has a daily run sheet of payments received.

The amount due needs to be the regular game fee plus any overdue fees. Once payment has been made either via cash or bank it should then calculate how much is still owing on the account so that it can be added to their next game.

Any (and really, I mean any lol) help appreciated!

**Taylor_1978** · 10-03-2007, 07:49 PM

Gee, thanks for the help! lol... Not only no help in almost a week, but only 1 person (I was the other one) bothered to even take a look at the file.

Thanks... Guess I'll go look elsewhere.