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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" 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 InsertNameDefine
Name: Sheet_Names
Refers to:

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

Then:

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

Biff

"Gdcprogrc" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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 InsertNameDefine
Name: Sheet_Names
Refers to:

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

Then:

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

Biff

"Gdcprogrc" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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 InsertNameDefine
Name: Sheet_Names
Refers to:

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

Then:

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

Biff

"Gdcprogrc" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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,

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