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.

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.

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.

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.

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="Dir ector",Recipient3!C2="Director"),AVERAGE(Recipient 1:Recipient3!U8),"no")


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



"Bernie Deitrick" wrote:

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.

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(Reci pient1:Recipient100!U8),"")

HTH,
Bernie
MS Excel MVP


"Danielle" wrote in message
...
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="Dir ector",Recipient3!C2="Director"),AVERAGE(Recipient 1:Recipient3!U8),"no")


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



"Bernie Deitrick" wrote:

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.

Thank you - it was the first option and it works.

"Bernie Deitrick" wrote:

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(Reci pient1:Recipient100!U8),"")

HTH,
Bernie
MS Excel MVP


"Danielle" wrote in message
...
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="Dir ector",Recipient3!C2="Director"),AVERAGE(Recipient 1:Recipient3!U8),"no")


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



"Bernie Deitrick" wrote:

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.