IF function over Multiple Worksheets

=OR(NOT(ISNA(MATCH("verify",'Alpha'!b10:c20))),NOT(ISNA(MATCH("verify",'Beta'!b10:c20))),NOT(ISNA(MATCH("verify",'Gamma'!B10:c20))),NOT(ISNA(MATCH("verify",'Delta'!b10:c20))))

Should do it.

However, that's pretty nasty, as you can see, and becomes unworkable if you have more than a handful of sheets....

If you are open to a quick VBA Function it can be much cleaner....

the morefunc add (see recommended addins & links thread at the top of the board) includes a countif.3d() funciton for precisely this sort of thing.

an alternative approach here:

http://www.mrexcel.com/board2/viewtopic.php?t=29977&postdays=0&postorder=asc&&start=10

Paddy

The morefunc addin will not embed to the workbook, I'm running Office Excel 2003 on XP Pro. I can only get countif.3d formula to work if I manually run morefunc.XLL, but it still will not embed into the workbook (so if I sent this off to someone else the function won't work for them).

And the alternative approach, Juan Pablo's formula, I cannot figure out how to apply it to multiple worksheets to use an "IF" function.



Please help if you can!

Paddy

The morefunc addin will not embed to the workbook, I'm running Office Excel 2003 on XP Pro. I can only get countif.3d formula to work if I manually run morefunc.XLL, but it still will not embed into the workbook (so if I sent this off to someone else the function won't work for them)...

Click to expand...

I just tried on Excel 2003: the add-in gets included successfully.

Alternatively...

Create a list of the relevant sheets in some range and name the range SheetList, then invoke:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!B10:C20"),"Verify"))

I tried reinstalling the add-in morefunc, and it failed to embed again.
This is what it says when I try manually installing (it didn't automatically install in the first place, so I followed instructions on morefunc.xls sheet):

"Morefunc n'apparait pas dans la liste des macros complementaires.
Veuillez l'ajouter par le menu Outils =>Macros complementaires => Parcourir"

Then when I try to use Tools|Add-In|Browse...morefunc.xll I get this message:

'C:Program Files\Morefunc\Morefunc.xll' is not a valid add-in.

Again, the only way I can get this to work is by opening morefunc.xll separately, but the problem remains - it won't embed into the workbook.


Next I tried the alternative approach you provided, but here is what happens:

Insert|Name|Define...
Sheetlist refers to =Sheet1:Sheet2!$B$10:$C$20

Cell D8 contains your formula and returns: #VALUE!
it looks like INDIRECT portion of formula is returning #value, array=volatile.

Sheet 1
Col B Col C
apple OK
orange OK
cherry ok
cucumber verify
pineapple ok
celery verify
melon OK
asparagus verify
potato verify
lemon OK
strawberry OK

Sheet 2
Col B Col C
radish verify
blueberry OK
kumquat OK
kiwi OK
guava ok
onion verify
garlic verify
sage verify
melon OK
lemon OK
strawberry OK

Could you clarify please? Thank you!!!

"Create a list of the relevant sheets in some range..."

Don't use the insert | name | define route - just stick the sheet names in some cells.

I tried reinstalling the add-in morefunc, and it failed to embed again.
This is what it says when I try manually installing (it didn't automatically install in the first place, so I followed instructions on morefunc.xls sheet):

"Morefunc n'apparait pas dans la liste des macros complementaires.
Veuillez l'ajouter par le menu Outils =>Macros complementaires => Parcourir"

Then when I try to use Tools|Add-In|Browse...morefunc.xll I get this message:

'C:Program Files\Morefunc\Morefunc.xll' is not a valid add-in.

Again, the only way I can get this to work is by opening morefunc.xll separately, but the problem remains - it won't embed into the workbook.


Next I tried the alternative approach you provided, but here is what happens:

Insert|Name|Define...
Sheetlist refers to =Sheet1:Sheet2!$B$10:$C$20

Cell D8 contains your formula and returns: #VALUE!
it looks like INDIRECT portion of formula is returning #value, array=volatile.

Click to expand...

You need a clean install such that you see Morefunc appear under Tools.

Sheet 1
Col B Col C
apple OK
orange OK
cherry ok
cucumber verify
pineapple ok
celery verify
melon OK
asparagus verify
potato verify
lemon OK
strawberry OK

Sheet 2
Col B Col C
radish verify
blueberry OK
kumquat OK
kiwi OK
guava ok
onion verify
garlic verify
sage verify
melon OK
lemon OK
strawberry OK

Could you clarify please? Thank you!!!

Click to expand...

Create a Summary sheet.

In A2:A3 enter:

Sheet1
Sheet2

Select A2:A3.
Go to the Name Box on the Formula Bar, type SheetList, and hit enter.

In C2 on Summary enter:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!C10:C20"),"Verify"))

This assumes that "Verify" appears in C10:C20 on every sheet if at all.

Still could not get the morefunc add-in installed. But that alternative method was......terrrrrrrrrrrrrIFIC!!!!

Thank you, Aladin!!!

Hello,

The morefunc addin will not embed to the workbook.

Click to expand...

Have you installed the latest Morefunc version (4.2) ? There are some changes related to this functionality.

Cordially,

Laurent