Referencing a named range based upon Range name entry in cell
 

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

Referencing a named range based upon Range name entry in cell
 

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

or


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



--
Regards,

Peo Sjoblom



"Barb Reinhardt" wrote in message
...
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

Referencing a named range based upon Range name entry in cell
 

INDIRECT(Sheet1!A1)

--
---
HTH

Bob

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



"Barb Reinhardt" wrote in message
...
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

Referencing a named range based upon Range name entry in cell
 

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

"Peo Sjoblom" wrote:

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

or


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



--
Regards,

Peo Sjoblom



"Barb Reinhardt" wrote in message
...
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

Referencing a named range based upon Range name entry in cell
 

It works for me


--
Regards,

Peo Sjoblom



"Barb Reinhardt" wrote in message
...
I tried something much like that and got a #REF error.

"Peo Sjoblom" wrote:

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

or


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



--
Regards,

Peo Sjoblom



"Barb Reinhardt" wrote in
message
...
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

Referencing a named range based upon Range name entry in cell
 

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

"Peo Sjoblom" wrote:

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

or


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



--
Regards,

Peo Sjoblom



"Barb Reinhardt" wrote in message
...
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

Referencing a named range based upon Range name entry in cell
 

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" wrote:

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

"Peo Sjoblom" wrote:

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

or


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



--
Regards,

Peo Sjoblom



"Barb Reinhardt" wrote in message
...
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

Referencing a named range based upon Range name entry in cell
 

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

"Duke Carey" wrote:

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" wrote:

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

"Peo Sjoblom" wrote:

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

or


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



--
Regards,

Peo Sjoblom



"Barb Reinhardt" wrote in message
...
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

Referencing a named range based upon Range name entry in cell
 

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

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


--
Regards,

Peo Sjoblom



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

"Duke Carey" wrote:

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" wrote:

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

"Peo Sjoblom" wrote:

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

or


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



--
Regards,

Peo Sjoblom



"Barb Reinhardt" wrote in
message
...
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

Referencing a named range based upon Range name entry in cell
 

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

"Bob Phillips" wrote:

INDIRECT(Sheet1!A1)

--
---
HTH

Bob

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



"Barb Reinhardt" wrote in message
...
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

Referencing a named range based upon Range name entry in cell
 

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" wrote:

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

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


--
Regards,

Peo Sjoblom



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

"Duke Carey" wrote:

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" wrote:

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

"Peo Sjoblom" wrote:

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

or


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



--
Regards,

Peo Sjoblom



"Barb Reinhardt" wrote in
message
...
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

Referencing a named range based upon Range name entry in cell
 

How does your BPQ range look under insertnamedefine?


--
Regards,

Peo Sjoblom



"Barb Reinhardt" wrote in message
...
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" wrote:

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

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


--
Regards,

Peo Sjoblom



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

"Duke Carey" wrote:

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" wrote:

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

"Peo Sjoblom" wrote:

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

or


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



--
Regards,

Peo Sjoblom



"Barb Reinhardt" wrote
in
message
...
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

Referencing a named range based upon Range name entry in cell
 

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

"Barb Reinhardt" wrote:

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

Referencing a named range based upon Range name entry in cell
 

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


"Barb Reinhardt" wrote:

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" wrote:

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

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


--
Regards,

Peo Sjoblom



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

"Duke Carey" wrote:

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" wrote:

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

"Peo Sjoblom" wrote:

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

or


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



--
Regards,

Peo Sjoblom



"Barb Reinhardt" wrote in
message
...
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

Referencing a named range based upon Range name entry in cell
 

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)



"Barb Reinhardt" wrote in message
...
My named range is also a dynamic named range. Its defined with offset
from
another dynamic named range.

"Bob Phillips" wrote:

INDIRECT(Sheet1!A1)

--
---
HTH

Bob

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



"Barb Reinhardt" wrote in
message
...
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