Here is one way...using INDIRECT()

Assuming in each sheet you have a header or a cell where you have mentioned
any date of the current month...Suppose cell A1 always hold a date of the
current month. Try this formula...From your post I understand your sheet
names are in the format June,July,August etc; hence "mmmm". If Jun,Jul,Aug
you need to change that to "mmm". If year is refered you can change this to
"mmmyy". Try and feedback.

=INDIRECT(TEXT(DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)),"mmmm") & "!E9")
The above formula should refer to the previous month E9

If this post helps click Yes
---------------
Jacob Skaria


"mjb1" wrote:

Is there a way around the fact that you cannot use a relative reference for
the Worksheet?
I want to use the ending balance from June to automatically populate into
the July opening balance. I have a new worksheet for each month. The
formulas copy and update except for the prior worksheet reference. Is there
a way to get the worksheet to look at the one before it to get these figures?
Right now I use =June!E9 for the July beginning balance, but when I copy
the worksheet to make one for August the formula stays =June!E9. I would
like it to update to =July!E9 (or whatever it takes to get that data) without
having to go through and manually change each line item.

Thanks for any input. I have been browsing the questions and answers here
and realize that I knew Office 97 pretty well, but am totally lost in Office
07!