Circular Reference Scenario?

[SCrowe]

Hi everyone!

I have a payroll spreadsheet that is based off a template. I save the template as a filename of PAY PERIOD 1 - 2008.XLS. In this spreadsheet, I have calculated cells to keep track of vacation hours for each employee: BEGINNING VAC BALANCE, VAC HRS EARNED THIS PAY, VAC HRS TAKEN THIS PAY, & ENDING VAC BALANCE.

My question is how could I automatically update NEXT pay period's calculations to show the ENDING VAC BALANCE from PAY PERIOD 1 - 2008.XLS to update in BEGINNING VAC BALANCE in PAY PERIOD 2 - 2008.XLS?

Another caveat would be we are continually hiring new people and/or people leave the company, so I have to add or remove them from these paysheets.

Would I need to create a separate spreadsheet to act as a bridge from PAY PERIOD 1 - 2008.XLS to PAY PERIOD 2 - 2008.XLS and if so how would circular reference have to come into play? Here's my spreadsheet:

THANKS IN ADVANCE FOR ANY INPUT!

PAYROLL TEMPLATE (NEW TEST).XLS
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	BI-WEEKLYPAYPERIODFOR11/28/07-12/11/07
2		EMPLOYEE:			SCHMO,JOE			STATUS:		FULLTIME	POSITION:	PARAMEDIC
3		DATE	DAYOFWEEK		HOURSWORKED	LUNCHTAKEN(L)	HOURLYWAGES				OTHEREARNINGS	TOTALOTHERS		COMMENTS		BEGINNINGVACBALANCE	HOURSEARNEDTHISPAY	HOURSTAKENTHISPAY	ENDINGVACBALANCE
4	WEEK1	11/28/07	WED		V8		TOTALREGHOURS	HRS	RATE/HR	REGPAY	VACATIONDAY(8HRS*$15/HR)	$120.00				32	4	8	28
5		11/29/07	THU		HOLIDAY			0:00	$15.00	$-	HOLIDAY(8HRS*$15/HR)	$120.00
6		11/30/07	FRI
7		12/01/07	SAT				TOTALOTHOURS	HRS	RATE/HR	OTPAY
8		12/02/07	SUN					0:00	$22.50	$-
9		12/03/07	MON
10		12/04/07	TUE										WEEK1TOTAL
11		WEEK1TOTALS					WEEK1HOURLYWAGES			$-	WEEK1OTHEREARNINGS	$240.00	$240.00
12
13	WEEK2	12/05/07	WED				TOTALREGHOURS	HRS	RATE/HR	REGPAY
14		12/06/07	THU					0:00	$15.00	$-
15		12/07/07	FRI
16		12/08/07	SAT				TOTALOTHOURS	HRS	RATE/HR	OTPAY
17		12/09/07	SUN					0:00	$22.50	$-				TOTALPAYCHECK(WEEK1+WEEK2)
18		12/10/07	MON
19		12/11/07	TUE										WEEK2TOTAL	$240.00
20		WEEK2TOTALS					WEEK2HOURLYWAGES			$-	WEEK2OTHEREARNINGS	$-	$-
MEDICS

 

[SCrowe]

Or do you guys think a DB would be a better way to go to do this application?

 

[SCrowe]

I was working on this problem quite a bit this weekend, but was unable to come up with a valid solution on my own. Does anyone have any ideas? I would greatly appreciate it! It would make my whole year!

Scott