locked
Consolidating Newly Created Worksheets RRS feed

  • Question

  • Using MS Excel 2007.

     

    I am working on a project that would consolidate data from different worksheets (budgets).

    My issue is that the users of the different workbooks will be creating new worksheets to reflect new financial periods. 

    Is there a method for the consolidating worksheet to detect the newly created worksheets and to adjust according? ie Displaying the data from the new worksheet in a new column in the consolidating worksheet.

     

    Thanks.

    Monday, June 27, 2011 5:53 PM

Answers

  • Melitta:

    This is not "the answer" to your questions, but I'll give you some insights.  What you are discussing could be a major development effort, requiring a significant amount of VBA.  In addition, your users would need to use a standard format so your consolidation program would be able to look for "landmarks".

    As far as detecting new sheets, I would have a hidden worksheet in my "master" consolidation program, and it would list the path to each of the foreign workbooks along with the names of all the worksheets within each foreign workbook.  This would be kept updated each time you ran the program. You can cycle through the sheets with code such as:

    For i = 1 to Sheets.Count

    Next i

    Again, what you are proposing is a major development effort, but it certainly is possible.

    Regards,

     


    Rich Locus Logicwurks, LLC www.logicwurks.com
    • Marked as answer by Calvin_Gao Tuesday, July 5, 2011 7:34 AM
    Tuesday, June 28, 2011 4:04 AM
  • Short of VBA coding, you could create a worksheet "template" that everyone must use as a starting point. Make it sure it has named Ranges with local scope so that each of these source worksheets have the same set of range names. Then reference these range names in the consolidating sheet. When new sheets are added from the template, just do a find and replace to include the new sheet's name reference in the formula:

    ='Sheet A'!OVERHEADCOSTS+'Sheet B'!OVERHEADCOSTS

    When Sheet C comes along, simply change 'Sheet B'!OVERHEADCOSTS to 'Sheet B'!OVERHEADCOSTS + 'Sheet C'!OVERHEADCOSTS

    I know this is a simplified view of things, but you might get an idea from it. As you can see, the use of Named Ranges abstracts you from the column and row references.

    What you really need is a persistent repository to track existing worksheets so that new worksheets can be detected. Typically this is done thru an XLA or XLAM or SaveSettings (registry setting).

     

    • Marked as answer by Calvin_Gao Tuesday, July 5, 2011 7:34 AM
    Tuesday, June 28, 2011 5:26 PM

All replies

  • Melitta:

    This is not "the answer" to your questions, but I'll give you some insights.  What you are discussing could be a major development effort, requiring a significant amount of VBA.  In addition, your users would need to use a standard format so your consolidation program would be able to look for "landmarks".

    As far as detecting new sheets, I would have a hidden worksheet in my "master" consolidation program, and it would list the path to each of the foreign workbooks along with the names of all the worksheets within each foreign workbook.  This would be kept updated each time you ran the program. You can cycle through the sheets with code such as:

    For i = 1 to Sheets.Count

    Next i

    Again, what you are proposing is a major development effort, but it certainly is possible.

    Regards,

     


    Rich Locus Logicwurks, LLC www.logicwurks.com
    • Marked as answer by Calvin_Gao Tuesday, July 5, 2011 7:34 AM
    Tuesday, June 28, 2011 4:04 AM
  • Short of VBA coding, you could create a worksheet "template" that everyone must use as a starting point. Make it sure it has named Ranges with local scope so that each of these source worksheets have the same set of range names. Then reference these range names in the consolidating sheet. When new sheets are added from the template, just do a find and replace to include the new sheet's name reference in the formula:

    ='Sheet A'!OVERHEADCOSTS+'Sheet B'!OVERHEADCOSTS

    When Sheet C comes along, simply change 'Sheet B'!OVERHEADCOSTS to 'Sheet B'!OVERHEADCOSTS + 'Sheet C'!OVERHEADCOSTS

    I know this is a simplified view of things, but you might get an idea from it. As you can see, the use of Named Ranges abstracts you from the column and row references.

    What you really need is a persistent repository to track existing worksheets so that new worksheets can be detected. Typically this is done thru an XLA or XLAM or SaveSettings (registry setting).

     

    • Marked as answer by Calvin_Gao Tuesday, July 5, 2011 7:34 AM
    Tuesday, June 28, 2011 5:26 PM