Danielle,

You can do it easily, but it requires a helper cell.

First, group your sheets (click on the first one's tab, then shift-click on the last one's tab), and
in cell E1 of the active sheet, enter the formula

=IF(E2="Partner",1,NA())

Then use the formula

=IF(ISERROR(SUM(Recipient1:Recipient100!E1)),"No", "Yes")

Change the sheet names in the above to reflect the names of your first and last sheets, and you're
done.

HTH,
Bernie
MS Excel MVP


"Danielle" wrote in message
...
This is great - and works - however we will have over 100 worksheets... is
there a simpler way? Else this AND statement will become VERy VERy long.


"Bernie Deitrick" wrote:

Danielle,

=IF(AND(Recipient1!E2="Partner",Recipient2!E2="Par tner",Recipient3!E2="Partner"), "yes","no")

HTH,
Bernie
MS Excel MVP


"Danielle" wrote in message
...
I am trying to reference other worksheet to be in the value of the lookup but
get #REF error. Is it not possible to reference other cells in multiple
worksheets

ie: =IF(Recipient1:Recipient3!E2="Partner", "yes","no")

I want to ask if e2 (on all worksheets) state the word "Partner" then say
Yes or No.

Eventually I want it to average the number together - but let's just start
with this simple question.

D.