virtualoverride
New Member
- Joined
- Dec 17, 2002
- Messages
- 2
I have a complicated problem. I will try to explain it as simply as I can but bear with me as some parts are a bit difficult to describe...
I have an Excel workbook which will contain a number of sheets. The number of sheets may vary from time to time. One sheet, named "Total" will contain the totals. The other sheets could be named anything; we may assume they are named "Sheet1", "Sheet2"...
The "Total" sheet should be able to SUM the numbers from all the other sheets. For instance:
A1 on Sheet1 = 1
A1 on Sheet2 = 4
A1 on Sheet3 = 2
=================
A1 on Total = 7
I could do it thus:
SUM(Sheet1!A1, Sheet2!A1, Sheet3!A1)
except that I do not know how many sheets there will be.
So there's the problem. Read on if you want to hear some of my ideas on ways to partially solve the problem.
One idea that I had was to list the sheet names in a row using a macro (I can handle the macro part). For instance:
<table border=1>
<tr align=center><td></td> <td>A</td> <td>B</td> <td>C</td></tr>
<tr align=center><td>15</td> <td>Sheet1</td> <td>Sheet2</td> <td>Sheet3</td></tr>
</table>
Then I tried stuff like this:
ADDRESS(ROW(), COLUMN(), 1, TRUE, A15)
which, if placed in cell A1 on "Total" would yield the value of 'Sheet1'!A1. However, this formula only yields the first value (the one in A15). I would still need some way to figure out how many sheets there are and SUM them up.
So, in summary:
- I need a function which, when placed in cell A1 on sheet "Total", will SUM the values of A1 on all other sheets. (When placed in A2, it will SUM the values of A2, etc.)
- The number of sheets is not known (though it can indirectly be figured out by using a macro; see next point)
- I can write macros, so if there is a way they must be integrated I have no problem with that. In fact, I know VB much better than I know workbook functions.
Thank you in advance for your help!
I have an Excel workbook which will contain a number of sheets. The number of sheets may vary from time to time. One sheet, named "Total" will contain the totals. The other sheets could be named anything; we may assume they are named "Sheet1", "Sheet2"...
The "Total" sheet should be able to SUM the numbers from all the other sheets. For instance:
A1 on Sheet1 = 1
A1 on Sheet2 = 4
A1 on Sheet3 = 2
=================
A1 on Total = 7
I could do it thus:
SUM(Sheet1!A1, Sheet2!A1, Sheet3!A1)
except that I do not know how many sheets there will be.
So there's the problem. Read on if you want to hear some of my ideas on ways to partially solve the problem.
One idea that I had was to list the sheet names in a row using a macro (I can handle the macro part). For instance:
<table border=1>
<tr align=center><td></td> <td>A</td> <td>B</td> <td>C</td></tr>
<tr align=center><td>15</td> <td>Sheet1</td> <td>Sheet2</td> <td>Sheet3</td></tr>
</table>
Then I tried stuff like this:
ADDRESS(ROW(), COLUMN(), 1, TRUE, A15)
which, if placed in cell A1 on "Total" would yield the value of 'Sheet1'!A1. However, this formula only yields the first value (the one in A15). I would still need some way to figure out how many sheets there are and SUM them up.
So, in summary:
- I need a function which, when placed in cell A1 on sheet "Total", will SUM the values of A1 on all other sheets. (When placed in A2, it will SUM the values of A2, etc.)
- The number of sheets is not known (though it can indirectly be figured out by using a macro; see next point)
- I can write macros, so if there is a way they must be integrated I have no problem with that. In fact, I know VB much better than I know workbook functions.
Thank you in advance for your help!
