| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| named dynamic range that ends with first text entry | Excel Worksheet Functions | |||
| inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
| How Do I Do an Exclude Filter based on a Named Range? | Excel Worksheet Functions | |||
| Return entries from one named range based on the contents of another | Excel Worksheet Functions | |||
| Define a range based on another named range | Excel Worksheet Functions | |||
Hybrid Mode
