IF Function: Lookup value needs to reference other worksheets

D

Danielle

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.
 
B

Bernie Deitrick

Danielle,

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

HTH,
Bernie
MS Excel MVP
 
D

Danielle

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.
 
B

Bernie Deitrick

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
 
D

Danielle

Thank you. So I changed the original formula slighty to be an OR rather then
AND and referencing different cells:

=IF(OR(Recipient1!C2="Director",Recipient2!C2="Director",Recipient3!C2="Director"),AVERAGE(Recipient1:Recipient3!U8),"no")


This works but as I said I will have over 100 worksheets. Can you exmplain
the helper file in this case.
 
B

Bernie Deitrick

Danielle,

Do you only want to average cell U8 from those sheets where C2 = "Director", or do you want to
average every cell U8 as long as any cell C2 on any sheet = "Director"?

In the first, the helper formula (let's say in cell C1) is
=IF(C2="Director",U8,"")
Then use
=AVERAGE(Recipient1:Recipient100!C1)

For the second, you need this in C1
=IF(C2="Director",1,0)
and the average formula becomes
=IF(SUM(Recipient1:Recipient100!C1)>0,AVERAGE(Recipient1:Recipient100!U8),"")

HTH,
Bernie
MS Excel MVP
 
Top