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

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

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

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

Ref Harlan's ..
You could use a list of worksheet names, e.g., WSLST referring to
={"Sheet2","Sheet3"}

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"smaruzzi" wrote:
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

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