Flagging and Displaying Data

[Gary Thomson]

Hi, there,

Suppose I have the following in Worksheet 1:

A B C D E F G
1 Staff Cost Number Tot. Cost Saving Net Cost Flag
2 Teacher £1000 10 £10,000 -£500 £9,500 1

but also the same in Worksheets 2 through 20, with the
numbers and costs changing, and also the flag value
changing (this can be either 1 or 0).


Now, I want a summary page which displays the information
in cells A2:F2 on each sheet, but only if the
corresponding flag variable on that sheet shows a "1".

Similarly, on the same sheet, (further down the page) I
would like the information in cells A2:F2 on each sheet to
be displayed together, so that I can total them up at the
bottom.

Is there a Macro that would do this??

 

[Tom Ogilvy]

Sub CopyData()
Dim sh As Worksheet
Dim rw As Long
rw = 1
For Each sh In Worksheets
If sh.Name <> "Summary" Then
If sh.Range("G2") = 1 Then
rw = rw + 1
Worksheets("Summary").Cells(rw, 1) _
.Resize(1, 6).Value = _
sh.Range("A2:F2").Value
End If
End If
Next
End Sub

--
Regards,
Tom Ogilvy


Hi, there,

Suppose I have the following in Worksheet 1:

A B C D E F G
1 Staff Cost Number Tot. Cost Saving Net Cost Flag
2 Teacher £1000 10 £10,000 -£500 £9,500 1

but also the same in Worksheets 2 through 20, with the
numbers and costs changing, and also the flag value
changing (this can be either 1 or 0).


Now, I want a summary page which displays the information
in cells A2:F2 on each sheet, but only if the
corresponding flag variable on that sheet shows a "1".

Similarly, on the same sheet, (further down the page) I
would like the information in cells A2:F2 on each sheet to
be displayed together, so that I can total them up at the
bottom.

Is there a Macro that would do this??

 

[Kevin Beckham]

Treat the 2nd part first
In summary sheet starting from cell A30

Sheet name Staff
31 Sheet1 =IF($A31<>"",INDIRECT($A31&"!A2"),"")
32 Sheet2 =IF($A32<>"",INDIRECT($A32&"!A2"),"")
etc
copying the formula across and changing !A2 to !B2 etc
to include column G!
ie cell H31 will be =IF($A31<>"",INDIRECT($A31&"!G2"),"")

Now at top of sheet
in cell A2 put =MATCH(1,H31:H50,0)
in cell A3 put =MATCH(1, OFFSET($H$31, A2, 0, 20, 1), 0)
and copy this down for 20 rows
in cell B2 put =IF(ISERROR($A2),"",OFFSET(B$30,$A2,0))
and copy this across and down to fill

Now hide column A

Kevin Beckham

 

[Kevin Beckham]

Correction
in cell A3 put =MATCH(1, OFFSET($H$31, A2, 0, 20, 1), 0) +
A2

Kevin Beckham