Can you use INDIRECT in 3-D references?

[Guest]

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.

 

[Harlan Grove]

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()))

 

[Biff]

Hi!

Try it like this:

C10 = ="A"&ROW()

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

Biff

 

[Guest]

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

 

[Biff]

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

In that case, use Harlan's suggestion.

Biff

 

[Guest]

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.

 

[Harlan Grove]

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.
....

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

 

[Biff]

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

 

[Guest]

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

 

[Guest]

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

Gdcprogrc wrote...
....

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()))

 

[Biff]

You're welcome!

Biff

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

 

[Guest]

Harlan,

how do you create a list of worksheet name? I tried Insert -> Names ->
Define , but I cannot physically enter the list of sheets names I want in the
Refers to entryfield because I don't know how to format it properly.
Should it be simply a reference to a cell containing the list of worksheets?

Thanks, Stefano

 

[Guest]

Ref Harlan's ..
Click Insert > Name > Define
Copy and paste the part: ={"Sheet2","Sheet3"}
directly into the "Refers to:" box
Then just enter into the "Names in workbook:" box: WSLST
and click OK

---