Referencing a named range based upon Range name entry in cell

[Barb Reinhardt]

Let's say I have cell Sheet1!A1 with the value of BPQ in it.
Let's also say that I have a named range with the name BPQ.

I'd like to reference that named range by referencing Sheet1!A1 somehow and
use it in a SUMPRODUCt statement. How would I reference the range?

Thanks in advance,
Barb Reinhardt

 

[Peo Sjoblom]

=SUMPRODUCT(--(INDIRECT(A1)>1))

or


=SUMPRODUCT(--(INDIRECT(Sheet1!A1)>1))

 

[Bob Phillips]

INDIRECT(Sheet1!A1)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Barb Reinhardt]

I tried something much like that and got a #REF error.

 

[Peo Sjoblom]

It works for me


--
Regards,

Peo Sjoblom

I tried something much like that and got a #REF error.

 

[Barb Reinhardt]

I should probably also say that BPQ is a workbook range, not a worksheet
range.

 

[Duke Carey]

This works for me:

=SUMPRODUCT(--(INDIRECT(Sheet1!D1)>5))

as long as the range name is global. If it gets changed so it is local to
Sheet1, it returns the #REF error

 

[Barb Reinhardt]

For this, the named range is functionally on Sheet2 and the cell with the
name is in Sheet1. Is that my problem?

 

[Peo Sjoblom]

Yes it is, you need somehow refer to the sheet then

=SUMPRODUCT(--(INDIRECT("'Sheet2'!"&A1)>1))

 

[Barb Reinhardt]

My named range is also a dynamic named range. Its defined with offset from
another dynamic named range.

 

[Barb Reinhardt]

Even if I have the range name defined as a workbook range I need to do that?
I've not had to reference workbook range names with the sheet names before.

 

[Peo Sjoblom]

How does your BPQ range look under insert>name>define?

 

[Barb Reinhardt]

Well, I've discovered the problem. When I hard code a test named range such
as "=Sheet1!B2:B10" it works, but when I use a dynamic range defined based on
OFFSET, it doesn't. Does anyone have any suggestion as to how I can address
this. I'm thinking I'll need a UDF.

Thanks,
Barb

 

[Duke Carey]

Use Tools->Fomula auditing->Evaluate Formula to find out which range name
fails, and where

 

[Bob Phillips]

How abut incorporating the offset formula in the SP

=SUMPRODUCT(--(OFFSET(Sheet2!A1,,,COUNTA(Sheet2!A:A),1)>1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)