I am trying to come up with a way to automatically accumulated calculate leave for each pay period in an Excel workbook. The way this is set up is that each week has it's own sheet within the workbook. For example, this pay period's sheet would pull the sum of last period's leave balance and subtract last period's leave used and add a set number of hours of newly earned leave which would then be reflected in this pay period's leave balance. I can easily enough come up with the formula, but I am stumped on how to automatically update the cell addresses in the formulas as new sheets are added for future pay periods.
Hope that makes sense. Thanks for any help...

This calls for the INDIRECT function.

What you need to do is to create a table like:
Column A----------Column B
NAME---------------SheetName
SUM-----------------SUM
Pay 1---------------Sheet 1
Pay 2---------------Sheet 2

Example:
To get the value in A1 for all Sheets:
=INDIRECT(CONCATENATE(B2,"!A1"))
=INDIRECT(CONCATENATE(B3,"!A1"))
=INDIRECT(CONCATENATE(B4,"!A1"))