Need to apply a function to a range of cells over a range of worksheets

[aecarr8286]

I have a workbook with several worksheets in the same format. I would like to have a function to output the sheets that have rows in which collumn A = X AND collumn B = Y.

I've been working on this one for a couple days now and I'm not making much progress. Any help would be greatly appreciated.

Thank!

 

[Domenic]

Are you looking to sum a column, based on the specified conditions, and for each sheet? If so, which column?

 

[aecarr8286]

Are you looking to sum a column, based on the specified conditions, and for each sheet? If so, which column?

Each worksheet contains data on a "trip". Each row in the worksheet describes an event on the trip. I would like to output a list of trips that contain an event with both an X in collumn A AND a Y in collumn B.

 

[Domenic]

Then I think a VBA solution would be more efficient. However, I'll have to defer to others when it comes to VBA.

 

[aecarr8286]

How about a count of sheets that contain an event that fulfills those conditions?

 

[aecarr8286]

Then I think a VBA solution would be more efficient. However, I'll have to defer to others when it comes to VBA.

Sadly my VBA skills are less than stellar.....

 

[Domenic]

Do those conditions occur more than once in any sheet or do they only occur once?

 

[aecarr8286]

Do those conditions occur more than once in any sheet or do they only occur once?

The event where both conditions are true may occur multiple times, only once or not at all in each sheet.

 

[Domenic]

First, list your sheet names in a horizontal range of cells, for example G2:I2. Then, define the following...

Insert > Name > Define

Name: Array1

Refers to:

=T(OFFSET(INDIRECT("'"&Sheet1!$G$2:$I$2&"'!A2:A100"),ROW(INDIRECT("2:100"))-2,0,1))

**Note that if Column A contains numerical values, change the 'T' in red to 'N'.

Click Add

Name: Array2

Refers to:

=T(OFFSET(INDIRECT("'"&Sheet1!$G$2:$I$2&"'!B2:B100"),ROW(INDIRECT("2:100"))-2,0,1))

**Note that if Column A contains numerical values, change the 'T' in red to 'N'.

Click Ok

Then try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(IF(MMULT(TRANSPOSE(ROW($A$2:$A$100)^0),IF(Array1="X",IF(Array2="Y",1,0),0)),1))

Note that if you'd prefer listing your sheet names in a vertical range of cells, for example G2:G4, replace...

Sheet1!$G$2:$I$2

with

TRANSPOSE(Sheet1!$G$2:$G$4)

Hope this helps!

xl-central.com

 

[aecarr8286]

Works like a charm!!!

Thanks a ton!

First, list your sheet names in a horizontal range of cells, for example G2:I2. Then, define the following...

Insert > Name > Define

Name: Array1

Refers to:

=T(OFFSET(INDIRECT("'"&Sheet1!$G$2:$I$2&"'!A2:A100"),ROW(INDIRECT("2:100"))-2,0,1))

**Note that if Column A contains numerical values, change the 'T' in red to 'N'.

Click Add

Name: Array2

Refers to:

=T(OFFSET(INDIRECT("'"&Sheet1!$G$2:$I$2&"'!B2:B100"),ROW(INDIRECT("2:100"))-2,0,1))

**Note that if Column A contains numerical values, change the 'T' in red to 'N'.

Click Ok

Then try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(IF(MMULT(TRANSPOSE(ROW($A$2:$A$100)^0),IF(Array1="X",IF(Array2="Y",1,0),0)),1))

Note that if you'd prefer listing your sheet names in a vertical range of cells, for example G2:G4, replace...

Sheet1!$G$2:$I$2

with

TRANSPOSE(Sheet1!$G$2:$G$4)

Hope this helps!

xl-central.com