Compiling data from multiple sheets
Compiling data from multiple sheets
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Compiling data from multiple sheets

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Oakland
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I'm creating a budgeting template that consists of 10 identical sheets (labeled Sheet1, Sheet2, etc.). Each sheet allows me to enter staff, materials, etc. for each phase of a project.

    I need to create a client report sheet, which pulls data from each sheet. That's easy. What I can't figure out is...

    How can I have the client report sheet ignore sheets that don't have anything on them? The algorithm is something like this:

    if cell g38 on sheet1 = 0 then ignore this one and go to the next sheet, else pull the data into the report.

    I'm hoping that the report sheet will flow smoothly (without gaps for missing sheets).

    Any suggestions?

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-16 17:49, PatMc wrote:
    I'm creating a budgeting template that consists of 10 identical sheets (labeled Sheet1, Sheet2, etc.). Each sheet allows me to enter staff, materials, etc. for each phase of a project.

    I need to create a client report sheet, which pulls data from each sheet. That's easy. What I can't figure out is...

    How can I have the client report sheet ignore sheets that don't have anything on them? The algorithm is something like this:

    if cell g38 on sheet1 = 0 then ignore this one and go to the next sheet, else pull the data into the report.

    I'm hoping that the report sheet will flow smoothly (without gaps for missing sheets).

    Any suggestions?
    Can you give more details? What data/how laid out on each sheet?

    A number of possiblities come to mind, but need to know more information. Most likely, with this many sheets and not all identically set up, a macro would be best.

    Jay

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    Oakland
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, here are the details. On each of the 10 sheets (named "Sheet1", "Sheet2", etc.), I have a text string in cell A3 and a dollar amount in cell G38. Now, it's possible that on a given project, I might use Sheet1, Sheet2, Sheet6, and Sheet8 ONLY.

    What I want to do is make it so that on my "Report" sheet, it checks each sheet to see if cell G38>0. If so, it will pull data from A3 and G38 and compile it on the Report sheet.

    For the sake of discussion, let's say that the data will go into two columns on the report sheet. So, for example...
    Sheet1!A3 will go into Report!A1
    Sheet1!G38 goes into Report!A2
    Sheet2!A3 goes into Report!B1
    Sheet2!G38 goes into Report!B2

    Now here's the tricky part...

    Sheet3, Sheet4, Sheet5 are unused for this example project, so...

    Sheet6!A3 goes into Report!C1
    Sheet6!G38 goes into Report!C2

    Sheet7 is unused, so the report ignores it.

    Sheet8!A3 goes into Report!D1
    Sheet8!G38 goes into Report!D2

    Sheet9 and Sheet10 are unused and ignored by Report.

    And finally, a sum of A2:D2 goes into E2.

    I hope that's clear (and possible).

    Thanks for your help.


  4. #4
    New Member
    Join Date
    Apr 2002
    Location
    Oakland
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oops... it's been a long day. In my example, I screwed up how I described which cells the data gets plugged into.

    It'll be two columns...

    A1, B1
    A2, B2
    A3, B3
    A4, B4

    with the sum of the B column in B5.

    Sorry for the confusion on my part.

  5. #5
    New Member
    Join Date
    Apr 2002
    Location
    Oakland
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Another approach occurred to me, though I don't know quite how to do this either. Instead of selectively pulling the data to the report sheet, perhaps it ALL is linked in... BUT, if a row contains a 0 value in column B, the entire row is hidden (or deleted).

    I know I could create a macro to hide a row, but there needs to be the evaluation of the value in column B to decided whether to hide it or not. I don't know how to do that.


  6. #6
    New Member
    Join Date
    Apr 2002
    Location
    Oakland
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Got it! I figured it out...

    Sub hideRow()

    For counter = 4 To 13
    If Cells(counter, 2).Value = 0 Then
    Rows(counter).Select
    Selection.EntireRow.Hidden = True
    End If
    Next

    End Sub

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com