How do I describe the current worksheet?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 said:
I think you'll need the INDIRECT() function.
--
David Biddulph

Martin Harriss said:
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
Click to expand...
Click to expand...
 
You can sum the data from the first and last sheets with:
=SUM(Sheet1!D20,INDIRECT(MID(CELL("filename",B2),FIND("]",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 said:
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?
Click to expand...
David Biddulph said:
I think you'll need the INDIRECT() function.
--
David Biddulph

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
You can sum the data from the first and last sheets with:
=SUM(Sheet1!D20,INDIRECT(MID(CELL("filename",B2),FIND("]",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 said:
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?
Click to expand...
David Biddulph said:
I think you'll need the INDIRECT() function.
--
David Biddulph

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top