increase a value in multiple spreadsheets without repeating a form

I would like to know if there is a way to select all sheets and select one
cell. Set a formula to increase from one sheet to the next. For instance,
=July 1, 2006+14. Have this automatically choose the previous sheet and add
14 days to the next sheet. Sheet one, july 1, 2006. Sheet 2, july 15, 2006.
sheet three, july 29, 2006. etc....

Jeanine

You can use a User Defined Function

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1.
Application.Volatile
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

Copy/paste this function to a general module in your workbook.

Enter the date July 1, 2006 in a cell of your choice in first sheet of workbook.
I will use B2 for this example.

Select second sheet then SHIFT + Click on last sheet in book. You now have all
sheets except first grouped.

Select B2 and enter =PrevSheet(B2) + 14

Click on tab of first sheet to ungroup the rest.


Gord Dibben MS Excel MVP


On Tue, 20 Jun 2006 12:53:02 -0700, Jeannine
<[email protected]> wrote:

>I would like to know if there is a way to select all sheets and select one
>cell. Set a formula to increase from one sheet to the next. For instance,
>=July 1, 2006+14. Have this automatically choose the previous sheet and add
>14 days to the next sheet. Sheet one, july 1, 2006. Sheet 2, july 15, 2006.
> sheet three, july 29, 2006. etc....

if you kept all your data in a database; instead of keeping it in 100
different documents; this might be quite easy.

it's called a simple cartesian in the database world.

-Aaron

Gord Dibben wrote:
> Jeanine
>
> You can use a User Defined Function
>
> Function PrevSheet(rg As Range)
> 'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1.
> Application.Volatile
> n = Application.Caller.Parent.Index
> If n = 1 Then
> PrevSheet = CVErr(xlErrRef)
> ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
> PrevSheet = CVErr(xlErrNA)
> Else
> PrevSheet = Sheets(n - 1).Range(rg.Address).Value
> End If
> End Function
>
> Copy/paste this function to a general module in your workbook.
>
> Enter the date July 1, 2006 in a cell of your choice in first sheet of workbook.
> I will use B2 for this example.
>
> Select second sheet then SHIFT + Click on last sheet in book. You now have all
> sheets except first grouped.
>
> Select B2 and enter =PrevSheet(B2) + 14
>
> Click on tab of first sheet to ungroup the rest.
>
>
> Gord Dibben MS Excel MVP
>
>
> On Tue, 20 Jun 2006 12:53:02 -0700, Jeannine
> <[email protected]> wrote:
>
> >I would like to know if there is a way to select all sheets and select one
> >cell. Set a formula to increase from one sheet to the next. For instance,
> >=July 1, 2006+14. Have this automatically choose the previous sheet and add
> >14 days to the next sheet. Sheet one, july 1, 2006. Sheet 2, july 15, 2006.
> > sheet three, july 29, 2006. etc....

It worked, thank you so much "Gord Dibben". My boss will think I am a
genius. You really know your stuff.

"Gord Dibben" wrote:

> Jeanine
>
> You can use a User Defined Function
>
> Function PrevSheet(rg As Range)
> 'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1.
> Application.Volatile
> n = Application.Caller.Parent.Index
> If n = 1 Then
> PrevSheet = CVErr(xlErrRef)
> ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
> PrevSheet = CVErr(xlErrNA)
> Else
> PrevSheet = Sheets(n - 1).Range(rg.Address).Value
> End If
> End Function
>
> Copy/paste this function to a general module in your workbook.
>
> Enter the date July 1, 2006 in a cell of your choice in first sheet of workbook.
> I will use B2 for this example.
>
> Select second sheet then SHIFT + Click on last sheet in book. You now have all
> sheets except first grouped.
>
> Select B2 and enter =PrevSheet(B2) + 14
>
> Click on tab of first sheet to ungroup the rest.
>
>
> Gord Dibben MS Excel MVP
>
>
> On Tue, 20 Jun 2006 12:53:02 -0700, Jeannine
> <[email protected]> wrote:
>
> >I would like to know if there is a way to select all sheets and select one
> >cell. Set a formula to increase from one sheet to the next. For instance,
> >=July 1, 2006+14. Have this automatically choose the previous sheet and add
> >14 days to the next sheet. Sheet one, july 1, 2006. Sheet 2, july 15, 2006.
> > sheet three, july 29, 2006. etc....
>
>