Relative Worksheet references

[mjb1]

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!

 

[Jacob Skaria]

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

 

[mjb1]

Thanks Jacob,

It works great for copy the formula from worksheet to worksheet, but not
down the rows within the worksheet. Entering the formula once on 50 lines
will be a lot easier than having to correct it for all 50 lines on each
months sheet. Unless you have another trick up your sleeve that will take
care of that too!

I did change my worksheet names to the "mmm" format to make it simpler.
Thanks again for your help.

 

[Jacob Skaria]

Try the below formula which will refer to E1 and when you copy down change to
E2,E3 and so on..The trick here is use the ROW function.. If you want to
refer the same ROW then use ROW() instead of ROW(A1)..

=INDIRECT(TEXT(DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)),"mmmm") & "!E" & ROW(A1))

You should be able to adjust this by changing the refenence cell within
...ROW()

If this post helps click Yes

 

[mjb1]

Thank-you Jacob,

It works like a charm!!!