Referencing a named range based upon Range name entry in cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
INDIRECT(Sheet1!A1)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
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
 
For this, the named range is functionally on Sheet2 and the cell with the
name is in Sheet1. Is that my problem?
 
Yes it is, you need somehow refer to the sheet then

=SUMPRODUCT(--(INDIRECT("'Sheet2'!"&A1)>1))
 
My named range is also a dynamic named range. Its defined with offset from
another dynamic named range.
 
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.
 
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
 
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Set non-continuous PrintArea via Function / Name 0
Drop Down with dynamic named range offsheet source list 14
How do I have a cell value define a dynamic named range? 15
A find formul looking at two named ranges for two strings 6
Name Ranges 6
Go To a Named Range (2nd posting) 4
Dynamic Named Ranges 3
Dependent drop down dynamic range formula, off sheet. 7

Back
Top