Testing for the presence of a value in multiple sheets to determine the total number of sheets with a value

Our bookkeepers have 100 sheets labeled:

YYMM-JE-PG-1,  YYMM-JE-PG-2, YYMM-JE-PG-3, YYMM-JE-PG-4, etc.
Each sheet contains a Page 1 or ?, Page 2 of ?, Page 3 of ? where the ? occupies a unique cell.
Each sheet populated in order by an entry in cell E9, so, if E9 in sheet YYMM-JE-PG-1 has a value, at least 1 sheet in being used, ie. Page 1 of 1.
E9 in sheet YYMM-JE-PG-2 has a value, at least 2 sheets are being used, ie. Page 1 of 2 and the Page 2 of 2
E9 in sheet YYMM-JE-PG-3 has a value, at least 3 sheets are being used, ie. Page 1 of 3, Page 2 of 3 and Page 3 of 3
E9 in sheet YYMM-JE-PG-4 has a value, at least 4 sheets are being used, ie. Page 1 of 4, Page 2 of 4 and Page 3 of 4, etc.
(The first sheet is a SETUP sheet which contains many items of information regarding PG-1, PG-2, etc.
I need a formula to put in a cell of the SETUP sheet which calculates the total number of sheets where E9 of each sheet contains a value.
I realize it will be a compound IF statement, unless some COUNT feature works across multiple sheets.
I could conceivably reserve an area in the SETUP sheet and test to see if E9 of each sheet has a value but I am unsure as to how to word the IF statement when there may not be as many sheets in the range I need to test, which would be sheets YYMM-JE-PG-1 through YYMM-JE-PG-100.

and then do a count.
LVL 1
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Cost AccountantCommented:
Have you simply tried:

=SUM('YYMM-JE-PG-1:YYMM-JE-PG-100'!E9)

or

=COUNT('YYMM-JE-PG-1:YYMM-JE-PG-100'!E9)
Executive Managing MemberAuthor Commented:
I will try it.  I testing several alternative I have noticed, for some reason, formula do not like the PG-1, PG-2, etc. The formulas want to place a ' around the 1, 2, 3, etc.  Changing the PG1, PG2, PG3, seems to fix the problem.  I will try both ways with your suggestion.
Executive Managing MemberAuthor Commented:
Your first formula returns a #REF
Your second formula returns and 0 when it should be 6
"Batchelor", Developer and EE Topic AdvisorCommented:
I have no issues with the original sheet names, both formulas are working fine. The second one is correct, as it should only be checked if something is in that cell.
Of course both formulas require you to have at least those both sheet names defined at any time, and the other sheets between them.
Cost AccountantCommented:
Bill, are you able to post a copy of a file with dummy data.
We should then be better able to ensure a tailored solution.
Executive Managing MemberAuthor Commented:
The formulas appear on the MEMOandSETUP at H95 and H96.  Below that is my lame attempt to setup a count sequence by testing each sheet for the presence of a value in E9.

Thanks,

Bill
New-Journal-Entry-Master--RBASEGOE10GA--
"Batchelor", Developer and EE Topic AdvisorCommented:
As mentioned your issue is that you do not have the sheet used as end of the range defined yet. There are 6 *PG* sheets only, and you need 100, or at least the first (-1) and last (-100) one. More precisely, any explicitly named sheet has to exist.
Executive Managing MemberAuthor Commented:
Renamed file is attached
JE-TEST1.xls
Executive Managing MemberAuthor Commented:
Ok, I have 1, 2, 3, 4 and 100.  Nothing changed.
JE-TEST2.xls
"Batchelor", Developer and EE Topic AdvisorCommented:
Just retype the range in the formula (or replace it completely). Your tries have messed it up. And of course use COUNT instead of SUM
=COUNT('YYMMDD-JE-PG-1:YYMM-JE-PG-100'!E9)
Cost AccountantCommented:
I am seeing some weird behaviour I would normally associate with table references.
I do not know why, but the formula references are messing up as I enter them.

Qlemo... are you getting some weird behaviour on the file?
"Batchelor", Developer and EE Topic AdvisorCommented:
I have to replace the formula, simply editing does not help, but aside from that, nothing strange.
Cost AccountantCommented:
Ah, well.. the sheet names are not consistent...

I had to change the first sheet name:

=SUM('YYMM-JE-PG-1:YYMM-JE-PG-6'!O2)

=COUNT('YYMM-JE-PG-1:YYMM-JE-PG-6'!O2)
U--JE-TEST1.xls
Cost AccountantCommented:
bill, this may be easier (more flexible) in VBA as a User Defined Formula:

For Each sht In ThisWorkbook.Sheets
If sht.name Like "YYMM*" Then
End If
Next sht

End Function

Use the function as : =sumCellAddress("E9")

The fucntion code will need to go into it's own module (not a sheet module)
Cost AccountantCommented:
Of if you just need to count:

For Each sht In ThisWorkbook.Sheets
If sht.name Like "YYMM*" Then
If len(sht.range("E9").Value) > 1 then
End if
End If
Next sht

End Function

Executive Managing MemberAuthor Commented:
Qlemo and Steve...This is my fault.  While I have been working on this issue, I was also working on a procedure to rename the tabs replacing the YY/MM/DD with, for example, 15/04/30.

That, of course, screws up what you have been trying to accomplish.  My mistake and I apologize.

That being said, you can depend the sheets to be tested always being named ??????-JE-PG-

Attached is the latest iteration which implements the renaming of tabs.
Journal-Entry-Mast-WithTabNaming.xls
Cost AccountantCommented:
Would you be agreeable to using VBA rather than a Formula approach?

This would be simple enough to do and be more flexible.

For Each sht In ThisWorkbook.Sheets
If sht.name Like "*-JE-PG-*" Then
If len(sht.range("E9").Value) > 1 then
End if
End If
Next sht

End Function

This should be an OK start.
Executive Managing MemberAuthor Commented:
I am agreeable.  I am just not sure of how to add another VBA (subroutine<==this phrase of course ages me) to the existing VBA (subroutines?) already there.  I will try.
Executive Managing MemberAuthor Commented:
I added the routine easily enough, but I am confused.  Where does the count show up?
Cost AccountantCommented:
You use the formula as you would any other formula...

Type into a cell : =countCellAddress("E9")
This should count all sheets with something in E9.

It will behave and appear as any other function.
The attached file has exmple.
U--Journal-Entry-Mast-WithTabNaming.xls

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Executive Managing MemberAuthor Commented: