I want to add the sum of cell D20 in all worksheets from the first up to the
current. I have tried:

=SUM(Sheet1:MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,256)!D20)

but I get an error.

Putting =MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,256) into a
separate cell returns the current workspace name, but it doesn't work in the
formula.

Any of you experts have any ideas?

Martin Harriss
Cambridge UK
--
Computer Graphics - CAD Specialist - Internet Information Design

Cambridge United Kingdom

I think you'll need the INDIRECT() function.
--
David Biddulph

"Martin Harriss" wrote in message
...
I want to add the sum of cell D20 in all worksheets from the first up to
the
current. I have tried:

=SUM(Sheet1:MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,256)!D20)

but I get an error.

Putting =MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,256) into
a
separate cell returns the current workspace name, but it doesn't work in
the
formula.

Any of you experts have any ideas?

Martin Harriss
Cambridge UK
--
Computer Graphics - CAD Specialist - Internet Information Design

Cambridge United Kingdom

David,

Thannk you for your information. I have read the spec of the INDIRECT()
function, and experimented, but can make no sense of it. Can you offer a
better example than the one in the Excel 2003 help system?

Martin Harriss
--
Computer Graphics - CAD Specialist - Internet Information Design

Cambridge United Kingdom


"David Biddulph" wrote:

I think you'll need the INDIRECT() function.
--
David Biddulph

"Martin Harriss" wrote in message
...
I want to add the sum of cell D20 in all worksheets from the first up to
the
current. I have tried:

=SUM(Sheet1:MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,256)!D20)

but I get an error.

Putting =MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,256) into
a
separate cell returns the current workspace name, but it doesn't work in
the
formula.

Any of you experts have any ideas?

Martin Harriss
Cambridge UK
--
Computer Graphics - CAD Specialist - Internet Information Design

Cambridge United Kingdom

You can sum the data from the first and last sheets with:
=SUM(Sheet1!D20,INDIRECT(MID(CELL("filename",B2),F IND("]",CELL("filename",B2))+1,256)&"!D20"))
but I haven't managed to sort out getting it to sum over the full range.

Someone else may be able to fathom out how to do that, but the thread at
http://preview.tinyurl.com/3am5kc implies that there may be difficulties.
--
David Biddulph

"Martin Harriss" wrote in message
...
David,

Thannk you for your information. I have read the spec of the INDIRECT()
function, and experimented, but can make no sense of it. Can you offer a
better example than the one in the Excel 2003 help system?

"David Biddulph" wrote:

I think you'll need the INDIRECT() function.
--
David Biddulph

"Martin Harriss" wrote in
message
...
I want to add the sum of cell D20 in all worksheets from the first up to
the
current. I have tried:

=SUM(Sheet1:MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,256)!D20)

but I get an error.

Putting =MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,256)
into
a
separate cell returns the current workspace name, but it doesn't work
in
the
formula.

Any of you experts have any ideas?

Martin Harriss
Cambridge UK
--
Computer Graphics - CAD Specialist - Internet Information Design

Cambridge United Kingdom

David,

This doesn't work I'm afraid.

To sum all the pages, I have created a worksheet called Sheet99 which I keep
at the end, so this formula sums all the pages:

=SUM(Sheet1:Sheet99!D20)

What I want to do is to sum all the sheets up to the current one, so I can
see the running total as I read the sheets.

So I was trying to substitute Sheet99 with the

MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,256)

construct. THis doesn't work, neither does enclosing it in an INDIRECT
function.

Perhape it's impossible to automate, and I have to modify the formula
explicitly on each sheet. This is an unexpected extra task!

Martin


--
Computer Graphics - CAD Specialist - Internet Information Design

Cambridge United Kingdom


"David Biddulph" wrote:

You can sum the data from the first and last sheets with:
=SUM(Sheet1!D20,INDIRECT(MID(CELL("filename",B2),F IND("]",CELL("filename",B2))+1,256)&"!D20"))
but I haven't managed to sort out getting it to sum over the full range.

Someone else may be able to fathom out how to do that, but the thread at
http://preview.tinyurl.com/3am5kc implies that there may be difficulties.
--
David Biddulph

"Martin Harriss" wrote in message
...
David,

Thannk you for your information. I have read the spec of the INDIRECT()
function, and experimented, but can make no sense of it. Can you offer a
better example than the one in the Excel 2003 help system?

"David Biddulph" wrote:

I think you'll need the INDIRECT() function.
--
David Biddulph

"Martin Harriss" wrote in
message
...
I want to add the sum of cell D20 in all worksheets from the first up to
the
current. I have tried:

=SUM(Sheet1:MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,256)!D20)

but I get an error.

Putting =MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,256)
into
a
separate cell returns the current workspace name, but it doesn't work
in
the
formula.

Any of you experts have any ideas?

Martin Harriss
Cambridge UK
--
Computer Graphics - CAD Specialist - Internet Information Design

Cambridge United Kingdom