# 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?
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.

Commented:
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)
0
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.
0
Executive Managing MemberAuthor Commented:
Your first formula returns a #REF
Your second formula returns and 0 when it should be 6
0
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.
0
Commented:
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.
0
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--
0
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.
0
Executive Managing MemberAuthor Commented:
Renamed file is attached
JE-TEST1.xls
0
Executive Managing MemberAuthor Commented:
Ok, I have 1, 2, 3, 4 and 100.  Nothing changed.
JE-TEST2.xls
0
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)
0
Commented:
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?
0
Batchelor, Developer and EE Topic AdvisorCommented:
I have to replace the formula, simply editing does not help, but aside from that, nothing strange.
0
Commented:
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
0
Commented:
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
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)
0
Commented:
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
End if
End If
Next sht

End Function
``````

0
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
0
Commented:
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
End if
End If
Next sht

End Function
``````

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

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:
0
Commented:
The journey can sometimes be more enlightening than the destination :)
Happy to be of assistance,
ATB
Steve.
0
###### 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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.