Compiling data from multiple sheets

[PatMc]

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?

 

[Jay Petrulis]

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

 

[PatMc]

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.

 

[PatMc]

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.

 

[PatMc]

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.

 

[PatMc]

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