+ Reply to Thread
Results 1 to 4 of 4

increase a value in multiple spreadsheets without repeating a form

  1. #1
    Jeannine
    Guest

    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....

  2. #2
    Gord Dibben
    Guest

    Re: increase a value in multiple spreadsheets without repeating a form

    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
    <Jeannine@discussions.microsoft.com> 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....



  3. #3
    dbahooker@hotmail.com
    Guest

    Re: increase a value in multiple spreadsheets without repeating a form


    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
    > <Jeannine@discussions.microsoft.com> 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....



  4. #4
    Jeannine
    Guest

    Re: increase a value in multiple spreadsheets without repeating a

    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
    > <Jeannine@discussions.microsoft.com> 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....

    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1