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
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

SteveCost 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)
Bill GoldenExecutive 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.
Bill GoldenExecutive Managing MemberAuthor Commented:
Your first formula returns a #REF
Your second formula returns and 0 when it should be 6
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Qlemo"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.
SteveCost 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.
Bill GoldenExecutive 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--
Qlemo"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.
Bill GoldenExecutive Managing MemberAuthor Commented:
Renamed file is attached
JE-TEST1.xls
Bill GoldenExecutive Managing MemberAuthor Commented:
Ok, I have 1, 2, 3, 4 and 100.  Nothing changed.
JE-TEST2.xls
Qlemo"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)
SteveCost 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?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I have to replace the formula, simply editing does not help, but aside from that, nothing strange.
SteveCost 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)

Open in new window


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

Open in new window

U--JE-TEST1.xls
SteveCost AccountantCommented:
bill, this may be easier (more flexible) in VBA as a User Defined Formula:

Function sumCellAddress(cellAddress As String) As Double

For Each sht In ThisWorkbook.Sheets
    If sht.name Like "YYMM*" Then
        sumCellAddress = sumCellAddress + sht.range("E9").Value
    End If
Next sht

End Function

Open in new window


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

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

Function countCellAddress(cellAddress As String) As Double

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

End Function

Open in new window


again used like: =countCellAddress("E9")
Bill GoldenExecutive 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
SteveCost AccountantCommented:
Would you be agreeable to using VBA rather than a Formula approach?

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

Function countCellAddress(cellAddress As String) As Double

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

End Function

Open in new window


This should be an OK start.
Bill GoldenExecutive 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.
Bill GoldenExecutive Managing MemberAuthor Commented:
I added the routine easily enough, but I am confused.  Where does the count show up?
SteveCost 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

Your issues matter to us.

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

Start your 7-day free trial
Bill GoldenExecutive Managing MemberAuthor Commented:
The Long and Winding Road...Thanks
SteveCost AccountantCommented:
The journey can sometimes be more enlightening than the destination :)
Happy to be of assistance,
ATB
Steve.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.