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.