How do I describe the current worksheet?
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 |
How do I describe the current worksheet?
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 |
How do I describe the current worksheet?
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 |
How do I describe the current worksheet?
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 |
How do I describe the current worksheet?
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 |
All times are GMT +1. The time now is 02:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com