Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,355
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,355
Default 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




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default 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








  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,355
Default 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




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,081
Default 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




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,355
Default 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



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default 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





  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,355
Default 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






  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,355
Default 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






  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default 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








  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,355
Default 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

  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,081
Default 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






  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default 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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
named dynamic range that ends with first text entry mcmanusb Excel Worksheet Functions 5 October 12th 06 05:33 AM
inserting a named range into new cells based on a named cell Peter S. Excel Discussion (Misc queries) 1 June 4th 06 03:53 AM
How Do I Do an Exclude Filter based on a Named Range? Dawg House Inc Excel Worksheet Functions 8 April 11th 06 02:49 AM
Return entries from one named range based on the contents of another Sam Crump Excel Worksheet Functions 1 March 6th 06 04:00 PM
Define a range based on another named range Basil Excel Worksheet Functions 2 February 21st 05 01:47 PM


All times are GMT +1. The time now is 08:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"