Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    OK, I anticipate having 12 worksheets of data, corresponding to the 12 months of the year. the data will be located in columns A-K, and the rows will vary by month between 100 and 350. In a separate section of each worksheet will be a section that will contain sumif/sumproduct formulas that will "look back" at the data in columns A through K. What I would like to do is to copy over January, rename it to February, then update that worksheet for February data. Again, the updated February data may result in more or less rows of data than January. What I would like to happen is that when I copy January over, the sumif/sumproduct formulas in the separate section will be automatically updated to the new number of rows in the February worksheet-otherwise I have to manually update, which is a very tedious task!
    Any ideas?
    Thanks i advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Why not create a single worksheet for all the data. Include a column for the date (or just the month). Your formulas can summarize the data by month.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788454' date='11-Aug-2009 11:32']Why not create a single worksheet for all the data. Include a column for the date (or just the month). Your formulas can summarize the data by month.[/quote]

    Thanks Hans. Unfortunately, the person responsible for the project would rather have the 12 individual worksheets...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Let's say that there will not be data in column A other than those in the data table.

    Activate the January sheet.
    Select Insert | Name | Define...
    Enter a name that includes the sheet name, e.g. January!Data
    In the refers to box, enter the following formula, again using the sheet name:

    =OFFSET(January!$A$1,0,0,COUNTA(January!$A:$A),11)

    Click OK.
    January!Data is a dynamic range; the number of rows will adjust itself automatically.
    You can now refer to January!Data in your formulas.

    When you copy the January sheet and rename the copy to February, the name January!Data will be copied with it and be renamed to February!Data, and formulas referring to January!Data will be adjusted automatically to refer to February!Test.

    Of course, you can define multiple names this way.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Hans, I'll give it a try.

Posting Permissions

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