I am trying to build a monthly worksheet that imports data from cells on
adjacent worksheets. Right now, I copy the worksheet, then have to manually
type in ('August 06'!G2,'Sept 06'!G2,'Oct 06'!G2). What I would like to do
is set it up so that it will take the current month G2, then add 1 and
subtract 1 to get the adjacent month's data so that it always shows a
rolling three month.

I've not been able to find a function that allows "worksheet+1"
"worksheet-1". Is this possible?

Sub getthreemonths()
pws = Sheets(ActiveSheet.Index - 1).Range("g2")
nws = Sheets(ActiveSheet.Index + 1).Range("g2")
MsgBox pws + Range("g2") + nws
End Sub

or put the below into a regular module in your workbook.
Function gtm()'just type =gtm() on the active sheet
pws = Sheets(ActiveSheet.Index - 1).Range("g2")
nws = Sheets(ActiveSheet.Index + 1).Range("g2")
gtm = pws + Range("g2") + nws
End Function

--
Don Guillett
SalesAid Software

"Brock Angelo" wrote in message
. ..
I am trying to build a monthly worksheet that imports data from cells on
adjacent worksheets. Right now, I copy the worksheet, then have to
manually
type in ('August 06'!G2,'Sept 06'!G2,'Oct 06'!G2). What I would like to do
is set it up so that it will take the current month G2, then add 1 and
subtract 1 to get the adjacent month's data so that it always shows a
rolling three month.

I've not been able to find a function that allows "worksheet+1"
"worksheet-1". Is this possible?

Previous Month:

=INDIRECT("'" &TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())),"mmmm
yy") & "'!G2")

Current Month
=INDIRECT("'" &TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY( ))),"mmmm
yy") & "'!G2")

Next Month
=INDIRECT("'" &TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODA Y())),"mmmm
yy") & "'!G2")

--
Regards,
Tom Ogilvy


"Brock Angelo" wrote:

I am trying to build a monthly worksheet that imports data from cells on
adjacent worksheets. Right now, I copy the worksheet, then have to manually
type in ('August 06'!G2,'Sept 06'!G2,'Oct 06'!G2). What I would like to do
is set it up so that it will take the current month G2, then add 1 and
subtract 1 to get the adjacent month's data so that it always shows a
rolling three month.

I've not been able to find a function that allows "worksheet+1"
"worksheet-1". Is this possible?

In each formula, change Day(today()) to the number 1 to avoid end of month
problems.

--
Regards,
Tom Ogilvy


"Brock Angelo" wrote:

I am trying to build a monthly worksheet that imports data from cells on
adjacent worksheets. Right now, I copy the worksheet, then have to manually
type in ('August 06'!G2,'Sept 06'!G2,'Oct 06'!G2). What I would like to do
is set it up so that it will take the current month G2, then add 1 and
subtract 1 to get the adjacent month's data so that it always shows a
rolling three month.

I've not been able to find a function that allows "worksheet+1"
"worksheet-1". Is this possible?

After closer reading of the question:

Previous Month:

=INDIRECT("'" &TEXT(DATE(YEAR(G2),MONTH(G2)-1,1),"mmmm yy") & "'!G2")



Next Month
=INDIRECT("'" &TEXT(DATE(YEAR(G2),MONTH(G2)+1,1),"mmmm yy") & "'!G2")

Format the cells as Date.



--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

Previous Month:

=INDIRECT("'" &TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())),"mmmm
yy") & "'!G2")

Current Month
=INDIRECT("'" &TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY( ))),"mmmm
yy") & "'!G2")

Next Month
=INDIRECT("'" &TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODA Y())),"mmmm
yy") & "'!G2")

--
Regards,
Tom Ogilvy


"Brock Angelo" wrote:

I am trying to build a monthly worksheet that imports data from cells on
adjacent worksheets. Right now, I copy the worksheet, then have to manually
type in ('August 06'!G2,'Sept 06'!G2,'Oct 06'!G2). What I would like to do
is set it up so that it will take the current month G2, then add 1 and
subtract 1 to get the adjacent month's data so that it always shows a
rolling three month.

I've not been able to find a function that allows "worksheet+1"
"worksheet-1". Is this possible?