Multiple Sheets Formula

[roc_ent]

Hi all, I wonder if someone can help me with this problem. I've created a workbook with 18 sheets. Each sheet is named 1-18 representing 18 tables of 8 people at a function. The tickets sold were either $30.00 or $15.00.

My 19th page is a total page which I've broken down to show each table on a separate row. I've created a =countif formula with the criteria of $30.00 for page 1, but the question I have is, can I use a command to have the next row down countif the same cells but on table 2 and so on.

So I want for example page 19 cell a1 will be =COUNTIF('1'!$J18:$L25,30) in cell a2 I want to copy of drag the formula but to change from "1'' TO "2''

Hope this is clear enough. Thanks in advance.

 

[Marcelo Branco]

Maybe...

A2 copied down
=COUNTIF(INDIRECT(ROWS(A$2:A2)&"!J18:L25"),30)

Hope this helps

M.

 

[ravishankar]

this may be an over kill but you can try these macro codes after inserting a sheet (19) called summary

Sub roc_ent()
Dim a As Integer
    For a = 1 To Sheets.Count
        If Sheets(a).Name <> "summary" Then
        Sheets("summary").Cells(a, 1) = Sheets(a).Name
        Sheets("summary").Cells(a, 2) = WorksheetFunction.CountIf(Sheets(a).Range("J18:L25"), 30)
        Sheets("summary").Cells(a, 3) = WorksheetFunction.CountIf(Sheets(a).Range("J18:L25"), 15)
        End If
    Next a
MsgBox "complete"
End Sub

run the macro.
ravi shankar