Thread: If Statement Across All Sheets

Here's my problem:

I have over 300 sheets in a workbook. 299 of them are identically set up and each contains the name of an office in cell A4 (though each sheet is simply named sheet1, sheet2, sheet3 etc). The last one is a calculations sheet. On the calculations sheet I have a list of the office names that starts at A1 and continues down column A.

What I would like to do is enter an IF statement in B1 (on the calculations sheet) that will search cell A4 on every sheet for the office name in A1 on the calculations sheet. When it finds a match I'd like it to pull out information from cell C7 of that sheet and enter it into cell B1 of the calculations sheet. I'd then like to copy this down for each entry in the list.

That's confusing so I'll try giving an example of what I need. This would be the sort of thing I'd like to enter in cell B1 on the calculations sheet (I know it doesn't make sense as it stands, I'm just using it as a visual representation):

=IF(ALLSHEETS!A4=CALCULATIONS!A1,FOUNDSHEET!C7,"")

I know it can probably be done by entering every sheet name, but with the number of sheets I'm working with it isn't practical. Thinking about it, it's proably a VBA thing, but I thought I'd explore this possibility first.

Any ideas?

Perhaps (not sure if 299 is too many sheets)?

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:299"))&"'!A4"),A1,INDIRECT("'Sheet"&ROW(INDIRECT ("1:299"))&"'!C7")))

You are most welcome Glad to help.