Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Consolidating Data from Multiple Worksheets (2003)

    After reading through the post 711913 and not being able to follow fully, I thought it appropriate to post my own question and workbook example. Please forgive me if I made an error in judgement.

    I would like to consolidate cell values from multiple data entry workbooks that all have the same data entry templates. The data entry workbooks all have a similar naming convention and their respective worksheets have similar naming conventions.

    In the attached workbook, I set up the logic that I am looking to mirror.

    The Consolidate.xls workbook would extract data from the same range of worksheets in DataEntry_1.xls, DataEntry_2.xls and DataEntry_3.xls and populate them as I have laid out in the tables on the Consolidate worksheet of the attachment. My hope is to populate the tables in Consolidate worksheet with the worksheet name from the data entry workbook and the most recent date of data entry.

    Each of the data entry workbooks have multiple worksheets: Template_1, Template_2 and so forth. I just use a one up numbering system across each data entry workbook.

    One catch is that each data entry workbook has one worksheet that doesn't follow the "Template" naming convention. I wouldn't want to extract data from that worksheet. Only those worksheets that begin with "Template".

    I have set up some conditional formatting in the tables on the Consolidate worksheet that didn't seem to take either. Different date formatting I guess.

    I know there is a lot here.

    Thanks
    Amy
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consolidating Data from Multiple Worksheets (2003)

    In C7 enter:
    =OFFSET(INDIRECT(B7&"!$C$8"),COUNTA(INDIRECT(B7&"! C$9:$C$28")),-1)

    Copy it down the column and in the other columns.

    If the sheet does not exist you will get an error... you could hide it with something like:
    =IF(ISERROR(INDIRECT(B7&"!A1")),"",OFFSET(INDIRECT (B7&"!$C$8"),COUNTA(INDIRECT(B7&"!C$9:$C$28")),-1))

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consolidating Data from Multiple Worksheets (2003)

    Concerning the conditionall formatting try the function:
    =OR(MONTH($C$2)<>MONTH(c7),YEAR($C$2)<>YEAR(c7))

    [You currently have no matches since not even June 3, 2008 equals June 1, 2008...]

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidating Data from Multiple Worksheets (2003)

    Steve,
    Thanks for the guidance and corrections to the attached workbook. The conditional formatting didn't seem to take as yet.

    However, instead of the formulas that reference worksheets in the attached workbook, I would like to call cell values from multiple data entry workbooks. For the attached example, I would like VB code to call open workbooks containing Template 1 and Template 2 and Template 5 and Template 6, populate the tables in a Consolidate workbook (imagine the Consolidate worksheet was a separate worksheet).

    The data entry workbooks all have a similar naming convention and their respective worksheets have similar naming conventions.

    Again, my hope is to be able to populate the tables in Consolidate worksheet (a separate workbook) with the worksheet name from the data entry workbook and the most recent date of data entry.

    Can a loop be written to access workbooks of specified naming conventions, extract worksheet names (of a specified naming convention) and specifed data ranges in the worksheet and populate those values in a separate workbook.

    Thanks
    Amy
    Attached Files Attached Files

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consolidating Data from Multiple Worksheets (2003)

    For the conditional formatting to work it should not go in the "cell Value is" but select "Formula is" from the first pulldown. Then you must edit it so it refers to the appropriate cells

    Yes a macro can be written to do those things. If you want help with that macro you will have to be more specific on the workbook naming convention (including folder to look in) the worksheet naming convention, what the specified data ranges are and where to populate the data. You don't provide any real details about what you want done and your setup so only general answers can be given.

    For looping thru file names, look at the VBA help (as well as searching WOPR) for code using DIR ...

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidating Data from Multiple Worksheets (2003)

    I have made the necessary correction the conditional formatting. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    Thanks for spelling out how I should outline the requirements. It's always best when the candy is put on the low shelves for us kids<img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    So here goes:

    I have multiple data entry workbooks with the following simlar naming convention:
    AMY_0.xls, AMY_1.xls, AMY_2.xls, AMY_3.xls, AMY_4.xls, AMY_5.xls.
    Each one of those workbooks has multiple data entry worksheets with the following similar naming conventions: DE_ABC, DE_BCD, DE_CDE, DE_*, etc. Each workbook also has one worksheet with the naming convention AMY_*_View (* is a wildcard for the 0, 1, 2, 3, 4 and 5 designator for the workbook).

    All data entry worksheets in the data entry workbooks start with the naming convention DE_*. And the data entry tables on those worksheets are all B9:C28 like the template worksheets in the attachment.

    I would like to have the Consolidate workbook (Consolidate worksheet) retrieve the values, place them in tables and apply them with the conditional formatting that we've created.

    All workbooks are contained in serv4home$amynMy Documents
    As I look at this, I would start with calling up each data entry workbook with a Do...Loop using the While and Until conditions to have it end eventually. Then use a Do...Loop with While and Until conditions to identify the worksheets that begin with DE_* within each data entry workbook and populate those worksheet names in the specified range of Consolidate.xls (Consolidate worksheet). Then a Do...Loop that would look at B9:C28 of each those worksheet and populate the specified values in specified ranges of the Consolidate workbook/worksheet.

    I'll continue to search WOPR to see if something similar has been done. Any and all further guidance and help is always appreciated.

    Amy
    Attached Files Attached Files

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consolidating Data from Multiple Worksheets (2003)

    so you start a loop on the computer
    find and open a workbook of the type AMY_0.xls in thh designated folder
    Then you find a worksheet matching the DE_* convention
    [If none exists, then close and go to next workbook]
    If the worksheet exists then
    from the range C9:C28 you find the last entry
    You get the date from Col B in the row with the last entry

    Now it seems less clear to me:
    Where do I put the date in the consolidate sheet? Do I search in B7:B13, E7:E16, and H7:H14 for a match to the sheetname and place it in the next column? [If found should the code check for an existing entry or just fill it in whether it is full or blank. }

    Do i just fill in one of the empty cells with the sheet name and date (how does the code know know which column? (dataEntry_1, 2, or 3)...


    After placing the date value in the appropriate cell, set the conditional formatting to check the cell's date vs the month and year of B2 (the day is not considered)
    Close the workbook
    find the next workbook and repeat...

    Is that what you are after?

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidating Data from Multiple Worksheets (2003)

    Steve,
    You are following well. I'll try to answer your questions here:

    The consolidate sheet should be in a separate workbook entirely within the same directory. You enter it and run the macros to retrieve the specified data from all of the AMY_*..xls workbooks and populate the retrieved data in some kind of table configuration where it lists the worksheet name (DE_*) from AMY_*.xls and the cell value from column B that corresponds to the available C9:C28 data. Take a look at how I set up the Consolidate worksheet in this updated Consolidate.xls workbook.

    I think you are getting me to better articulate my logic and requirements.

    If the worksheet name starts with "DE_", but there is no data in C9:C28, then it can populate with N/A or something like that. If the worksheet name doesn't start w/ DE_, then it should not try to return values from that worksheet to Consolidate.xls Consolidate worksheet.

    Thanks
    Amy
    Attached Files Attached Files

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consolidating Data from Multiple Worksheets (2003)

    You have changed the form of consolidate. Is this the form it will be?

    Will the filenames and sheets already be listed in these tables? If so there is no need to search for files, one can just look up the appropriate items from the tables.

    Which will it be.

    Should the search create the consolidate sheet in the pattern listed?

    Steve

  10. #10
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidating Data from Multiple Worksheets (2003)

    This newest consolidate form is fine. Listing everything in the same columns would be much easier to maintain.

    The filenames can be listed in the tables, but not the worksheet names. Over time, data entry worksheets will be added or deleted from each of the AMY_*.xls workbooks. As such, I would want the table to be able to expand and accommodate more worksheet listings and still have a space between the table for AMY_1.xls and AMY_2.xls and so forth.

    Having a consolidate sheet in the pattern posted would be super.

    Thanks
    Amy

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consolidating Data from Multiple Worksheets (2003)

    I am still a little confused. Is the macro going to maintain the list of workbooks and sheetnames or is someone going to maintain this list and just add new or replace items when the macro is run?

    In other words:
    should the entire sheet be overwritten and only list the workbooks and sheets found whent he code is run?
    Or Should the structure be maintained from the start and added to with each search:
    If the sheetname has a date already, should the old one be replaced or kept?
    If there is no matching sheetname found on the sheet, should the name and date be added or should the sheet be ignored?

    Pleas walk me through what the code should do for each open workbook. At this point the code would know the workbook name, the sheetname and have the lastdate from the open workbook and be working with consolidate workbook (where I presume the code will reside). What do you want the code to do with those pieces of info (especially what to do if there is no listing for workbook name and/or worksheet name in the list)

    As I mentioned before, if you want help with coding you must provide us details of what you want the code to do...
    Steve

  12. #12
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidating Data from Multiple Worksheets (2003)

    Sorry for not getting back to you right away.

    The code should be executed from Consolidate.xls.

    The macro is going to maintain the list of workbooks and sheetnames. I don't want anyone to have to maintain the list. It should add new or replace items when the macro is run.

    It seems to me that the Consolidate worksheet of Consolidate.xls would be overwritten and list the workbooks and worksheets found that meet the naming criteria "AMY_*.xls" and "DE_".
    It would probably be easier to maintain only the conditional formatting for the column B values that will populate and the =TODAY references in row 1.
    The sheet should be ignored if it does not match the naming convention that starts with "DE_".

    So the code will open AMY_1.xls, populate the Consolidate worksheet of Consolidate.xls with the workbook name "AMY_1" in A5 and then all of the worksheet names of AMY_1.xls that begin with "DE_" starting in A6. It will also populate the Consolidate worksheet with the date of the most recent data entry next to the cell where the worksheet name was populated starting in B6. If there is no data entered in a DE_ worksheet range B9:C28, it should populate the consolidate worksheet with N/A or No Data. That newly created range that was populated in the Consolidate worksheet would follow the conditional formatting rules that we went through. The AMY_1.xls workbook would then close of course.

    The code should then go through the same methods for AMY_2, AMY_3, AMY_4, AMY_5 and AMY_0. Those value ranges should be populated on Consolidate worksheet in columns A and B one row past the last entry from the previously populated AMY_* workbook.

    I can execute the Consolidate.xls macros from a button placed in somewhere in rows 1-4.

    Thanks
    Amy

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consolidating Data from Multiple Worksheets (2003)

    Does the attached code do what you want?

    Steve
    Attached Files Attached Files

  14. #14
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidating Data from Multiple Worksheets (2003)

    Steve,
    As though you had to ask <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

    The only thing I would like to add to this is adjusting the conditional formatting to include the TODAY() value OR a value from Consolidate worksheet cell B3. I have created a data validation menu in B3 from which to select values like "CY08 Q1", "CY08 Q2", "CY03 Q2", etc. Some of the date values in the AMY_*.xls DE_* worksheets have these type values instead of the mmm-yy value. How would that be inserted in the code you wrote below?

    'add conditional fomatting and number format
    ThisWorkbook.Activate
    rDates.Select
    With Selection
    .FormatConditions.Delete
    .FormatConditions.Add _
    Type:=xlExpression, _
    Formula1:= _
    "=OR(MONTH($A$1)<>MONTH(" & _
    .Cells(1).Address(False, False) & _
    "),YEAR($A$1)<>YEAR(" & _
    .Cells(1).Address(False, False) & "))"
    .FormatConditions(1).Interior.Color = vbRed
    .NumberFormat = "mmm-yy"
    End With

    I am amazed. Thanks.
    Amy

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Consolidating Data from Multiple Worksheets (2003)

    What exactly do you want the validation to do? right now it checks the month and year of the date and colors it red if it does not equal the month and year of today.

    What do you want to include in the validation?

    Perhaps you can provide a sample "consolidate sheet" with various possibilities (dates, "No Data", other types) and what you want to validate against and how to compare and what colors they should be.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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