Summary sheet with text entries.

[tgcali]

Hello,

I have a workbook with 235+ pages and counting. The pages are named Sheet1,
Sheet2, etc. to keep it simple.

I need to know how to find out if the word 'yes' was entered into the same
specific cell (B43) on each sheet and have the total number of times this
occured show on my summary sheet, automatically updating as data is entered
and new sheets are created. Is this possible?

Thank you,

tgcali

 

[Mike H]

Hi,

This is a bit long winded for all those sheets but I don't know of any other
way without resorting to a macro, perhaps someone else does.

Make a list of all the sheets in a column then select that list and

Insert|name|Define and call it (say) Sheets

Then enter this formula
=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!B43"),"Yes"))

Mike

 

[tgcali]

I'm sorry, but how do you mean to make a list? Manually, validation, I don't
understand. I do appreciate your help greatly!

 

[Mike H]

Hi,

As an afterthought perhaps not so long winded. To get your list of your
worksheets, right click any sheet, view code and paste this in to get a list
of all sheet names in column A from a1 down.

Sub namem()
x = 1
For Each Worksheet In ThisWorkbook.Worksheets
Cells(x, 1).Value = Worksheet.Name
x = x + 1
Next
End Sub

Mike

 

[tgcali]

Ok, I tried that. I think I just don't get this. I can't seem to make it
work. Thank you for your help though.

 

[tgcali]

I'm sorry. I'm a little dense today. I got it! Thanks so much. I really
appreciate it.