Can you use INDIRECT in 3-D references?

For example
=SUM(INDIRECT(C10))
where C10 would contain
="Sheet2:"&"Sheet3!"&"A"&ROW()
always returns #REF!.
However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine.

Gdcprogrc wrote...
>For example
>=SUM(INDIRECT(C10))
>where C10 would contain
>="Sheet2:"&"Sheet3!"&"A"&ROW()
>always returns #REF!.
....

No. INDIRECT can only return range references. 3D references are never
range references.

You could use a list of worksheet names, e.g., WSLST referring to

={"Sheet2","Sheet3"}

then use the formula

=SUMPRODUCT(N(INDIRECT("'"&WSLST&"'!A"&ROW()))

Hi!

Try it like this:

C10 = ="A"&ROW()

=SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT("2:3"))&"!"&C10)))

Biff

"Gdcprogrc" <Gdcprogrc@discussions.microsoft.com> wrote in message
news:E0898A3A-B2C8-42BA-A8D9-3EA3B6C9DD8B@microsoft.com...
> For example
> =SUM(INDIRECT(C10))
> where C10 would contain
> ="Sheet2:"&"Sheet3!"&"A"&ROW()
> always returns #REF!.
> However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine.

Thanks Biff. That seems to work.
What does one do if your worksheets are named, June, July, August etc.?

"Biff" wrote:

> Hi!
>
> Try it like this:
>
> C10 = ="A"&ROW()
>
> =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT("2:3"))&"!"&C10)))
>
> Biff
>
> "Gdcprogrc" <Gdcprogrc@discussions.microsoft.com> wrote in message
> news:E0898A3A-B2C8-42BA-A8D9-3EA3B6C9DD8B@microsoft.com...
> > For example
> > =SUM(INDIRECT(C10))
> > where C10 would contain
> > ="Sheet2:"&"Sheet3!"&"A"&ROW()
> > always returns #REF!.
> > However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine.
>
>
>

>What does one do if your worksheets are named, June, July, August etc.?

In that case, use Harlan's suggestion.

Biff

"Gdcprogrc" <Gdcprogrc@discussions.microsoft.com> wrote in message
news:2BAA6BBE-E70F-4A80-B7F0-B92F5593D5AD@microsoft.com...
> Thanks Biff. That seems to work.
> What does one do if your worksheets are named, June, July, August etc.?
>
> "Biff" wrote:
>
>> Hi!
>>
>> Try it like this:
>>
>> C10 = ="A"&ROW()
>>
>> =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT("2:3"))&"!"&C10)))
>>
>> Biff
>>
>> "Gdcprogrc" <Gdcprogrc@discussions.microsoft.com> wrote in message
>> news:E0898A3A-B2C8-42BA-A8D9-3EA3B6C9DD8B@microsoft.com...
>> > For example
>> > =SUM(INDIRECT(C10))
>> > where C10 would contain
>> > ="Sheet2:"&"Sheet3!"&"A"&ROW()
>> > always returns #REF!.
>> > However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine.
>>
>>
>>

Thanks, again, Biff. I was hoping not to have to use a defined name because
I wanted the name list length to change or be variable, which requires more
work. But I did like Harlan's suggestion for other applications.

"Biff" wrote:

> >What does one do if your worksheets are named, June, July, August etc.?
>
> In that case, use Harlan's suggestion.
>
> Biff
>
> "Gdcprogrc" <Gdcprogrc@discussions.microsoft.com> wrote in message
> news:2BAA6BBE-E70F-4A80-B7F0-B92F5593D5AD@microsoft.com...
> > Thanks Biff. That seems to work.
> > What does one do if your worksheets are named, June, July, August etc.?
> >
> > "Biff" wrote:
> >
> >> Hi!
> >>
> >> Try it like this:
> >>
> >> C10 = ="A"&ROW()
> >>
> >> =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT("2:3"))&"!"&C10)))
> >>
> >> Biff
> >>
> >> "Gdcprogrc" <Gdcprogrc@discussions.microsoft.com> wrote in message
> >> news:E0898A3A-B2C8-42BA-A8D9-3EA3B6C9DD8B@microsoft.com...
> >> > For example
> >> > =SUM(INDIRECT(C10))
> >> > where C10 would contain
> >> > ="Sheet2:"&"Sheet3!"&"A"&ROW()
> >> > always returns #REF!.
> >> > However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine.
> >>
> >>
> >>
>
>
>

Gdcprogrc wrote...
>Thanks, again, Biff. I was hoping not to have to use a defined name because
>I wanted the name list length to change or be variable, which requires more
>work. But I did like Harlan's suggestion for other applications.
....

My technique works with dynamic range names. Just make WSLST *long*,
enter only the worksheet names over which you want to sum at the top of
the list, and create another defined name like WSLST.EFFECTIVE (or
something shorter) defined as

=INDEX(WSLST,1):INDEX(WSLST,COUNTA(WSLST))

(no volatile function calls), and use WSLST.EFFECTIVE in place of
WSLST.

>>"Gdcprogrc" <Gdcprogrc@discussions.microsoft.com> wrote in message
....
>>>What does one do if your worksheets are named, June, July, August etc.?
....

Use TEXT(DATE(2006,{6;7;8;...},1),"mmmm") in place of
"Sheet"&ROW(INDIRECT("2:3")).

You can make the sheet name list a dynamic range list and use Harlan's
formula.......

List the sheet names in a range of cells, say, H1:Hn

Create a dynamic named range for the sheet names.
Goto Insert>Name>Define
Name: Sheet_Names
Refers to:

=OFFSET(Sheet1!$H$1,,,COUNTA(Sheet1!$H:$H))

Then:

=SUMPRODUCT(N(INDIRECT("'"&Sheet_Names&"'!"&C10)))

Biff

"Gdcprogrc" <Gdcprogrc@discussions.microsoft.com> wrote in message
news:1F2F5E6C-387F-438C-AFD6-85F875D729EF@microsoft.com...
> Thanks, again, Biff. I was hoping not to have to use a defined name
> because
> I wanted the name list length to change or be variable, which requires
> more
> work. But I did like Harlan's suggestion for other applications.
>
> "Biff" wrote:
>
>> >What does one do if your worksheets are named, June, July, August etc.?
>>
>> In that case, use Harlan's suggestion.
>>
>> Biff
>>
>> "Gdcprogrc" <Gdcprogrc@discussions.microsoft.com> wrote in message
>> news:2BAA6BBE-E70F-4A80-B7F0-B92F5593D5AD@microsoft.com...
>> > Thanks Biff. That seems to work.
>> > What does one do if your worksheets are named, June, July, August etc.?
>> >
>> > "Biff" wrote:
>> >
>> >> Hi!
>> >>
>> >> Try it like this:
>> >>
>> >> C10 = ="A"&ROW()
>> >>
>> >> =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT("2:3"))&"!"&C10)))
>> >>
>> >> Biff
>> >>
>> >> "Gdcprogrc" <Gdcprogrc@discussions.microsoft.com> wrote in message
>> >> news:E0898A3A-B2C8-42BA-A8D9-3EA3B6C9DD8B@microsoft.com...
>> >> > For example
>> >> > =SUM(INDIRECT(C10))
>> >> > where C10 would contain
>> >> > ="Sheet2:"&"Sheet3!"&"A"&ROW()
>> >> > always returns #REF!.
>> >> > However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine.
>> >>
>> >>
>> >>
>>
>>
>>

Thanks a lot, Biff. Huge help and I appreciate it!

"Biff" wrote:

> You can make the sheet name list a dynamic range list and use Harlan's
> formula.......
>
> List the sheet names in a range of cells, say, H1:Hn
>
> Create a dynamic named range for the sheet names.
> Goto Insert>Name>Define
> Name: Sheet_Names
> Refers to:
>
> =OFFSET(Sheet1!$H$1,,,COUNTA(Sheet1!$H:$H))
>
> Then:
>
> =SUMPRODUCT(N(INDIRECT("'"&Sheet_Names&"'!"&C10)))
>
> Biff
>
> "Gdcprogrc" <Gdcprogrc@discussions.microsoft.com> wrote in message
> news:1F2F5E6C-387F-438C-AFD6-85F875D729EF@microsoft.com...
> > Thanks, again, Biff. I was hoping not to have to use a defined name
> > because
> > I wanted the name list length to change or be variable, which requires
> > more
> > work. But I did like Harlan's suggestion for other applications.
> >
> > "Biff" wrote:
> >
> >> >What does one do if your worksheets are named, June, July, August etc.?
> >>
> >> In that case, use Harlan's suggestion.
> >>
> >> Biff
> >>
> >> "Gdcprogrc" <Gdcprogrc@discussions.microsoft.com> wrote in message
> >> news:2BAA6BBE-E70F-4A80-B7F0-B92F5593D5AD@microsoft.com...
> >> > Thanks Biff. That seems to work.
> >> > What does one do if your worksheets are named, June, July, August etc.?
> >> >
> >> > "Biff" wrote:
> >> >
> >> >> Hi!
> >> >>
> >> >> Try it like this:
> >> >>
> >> >> C10 = ="A"&ROW()
> >> >>
> >> >> =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT("2:3"))&"!"&C10)))
> >> >>
> >> >> Biff
> >> >>
> >> >> "Gdcprogrc" <Gdcprogrc@discussions.microsoft.com> wrote in message
> >> >> news:E0898A3A-B2C8-42BA-A8D9-3EA3B6C9DD8B@microsoft.com...
> >> >> > For example
> >> >> > =SUM(INDIRECT(C10))
> >> >> > where C10 would contain
> >> >> > ="Sheet2:"&"Sheet3!"&"A"&ROW()
> >> >> > always returns #REF!.
> >> >> > However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Thanks a lot, Harlan, you were a big help!

"Harlan Grove" wrote:

> Gdcprogrc wrote...
> >For example
> >=SUM(INDIRECT(C10))
> >where C10 would contain
> >="Sheet2:"&"Sheet3!"&"A"&ROW()
> >always returns #REF!.
> ....
>
> No. INDIRECT can only return range references. 3D references are never
> range references.
>
> You could use a list of worksheet names, e.g., WSLST referring to
>
> ={"Sheet2","Sheet3"}
>
> then use the formula
>
> =SUMPRODUCT(N(INDIRECT("'"&WSLST&"'!A"&ROW()))
>
>

You're welcome!

Biff

"Gdcprogrc" <Gdcprogrc@discussions.microsoft.com> wrote in message
news:6E6E9841-752A-4EC5-AB3C-2EC290944776@microsoft.com...
> Thanks a lot, Biff. Huge help and I appreciate it!
>
> "Biff" wrote:
>
>> You can make the sheet name list a dynamic range list and use Harlan's
>> formula.......
>>
>> List the sheet names in a range of cells, say, H1:Hn
>>
>> Create a dynamic named range for the sheet names.
>> Goto Insert>Name>Define
>> Name: Sheet_Names
>> Refers to:
>>
>> =OFFSET(Sheet1!$H$1,,,COUNTA(Sheet1!$H:$H))
>>
>> Then:
>>
>> =SUMPRODUCT(N(INDIRECT("'"&Sheet_Names&"'!"&C10)))
>>
>> Biff
>>
>> "Gdcprogrc" <Gdcprogrc@discussions.microsoft.com> wrote in message
>> news:1F2F5E6C-387F-438C-AFD6-85F875D729EF@microsoft.com...
>> > Thanks, again, Biff. I was hoping not to have to use a defined name
>> > because
>> > I wanted the name list length to change or be variable, which requires
>> > more
>> > work. But I did like Harlan's suggestion for other applications.
>> >
>> > "Biff" wrote:
>> >
>> >> >What does one do if your worksheets are named, June, July, August
>> >> >etc.?
>> >>
>> >> In that case, use Harlan's suggestion.
>> >>
>> >> Biff
>> >>
>> >> "Gdcprogrc" <Gdcprogrc@discussions.microsoft.com> wrote in message
>> >> news:2BAA6BBE-E70F-4A80-B7F0-B92F5593D5AD@microsoft.com...
>> >> > Thanks Biff. That seems to work.
>> >> > What does one do if your worksheets are named, June, July, August
>> >> > etc.?
>> >> >
>> >> > "Biff" wrote:
>> >> >
>> >> >> Hi!
>> >> >>
>> >> >> Try it like this:
>> >> >>
>> >> >> C10 = ="A"&ROW()
>> >> >>
>> >> >> =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT("2:3"))&"!"&C10)))
>> >> >>
>> >> >> Biff
>> >> >>
>> >> >> "Gdcprogrc" <Gdcprogrc@discussions.microsoft.com> wrote in message
>> >> >> news:E0898A3A-B2C8-42BA-A8D9-3EA3B6C9DD8B@microsoft.com...
>> >> >> > For example
>> >> >> > =SUM(INDIRECT(C10))
>> >> >> > where C10 would contain
>> >> >> > ="Sheet2:"&"Sheet3!"&"A"&ROW()
>> >> >> > always returns #REF!.
>> >> >> > However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work
>> >> >> > fine.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>