Array Fromula
 

Hi,

does anyone know why this formila isn't working?
{=SUM(INDIRECT(ADDRESS(C8;D8;;E8:E108)))}
What I want to do is this: I have a workbook that has around 100 sheets (all
of these sheets are equally arranged), so i want to sum all the figures from
a determined cell from every sheet.
In the above example the C8;D8 contain the row and column numbers to
identify the cell i want to sum and the array E8:E108 has the name of the
sheets.
I don't know if the adress function works with arrays. If anyone knows how i
can do this, please help.

thanks

Array Fromula
 

Perhaps a 3D reference?

=SUM(Sheet1:Sheet100!A1)

will sum cell A1 on Sheet1 through Sheet100 (change the beginning and ending
sheet names to whatever yours are called).


"Pmxgs" wrote:

Hi,

does anyone know why this formila isn't working?
{=SUM(INDIRECT(ADDRESS(C8;D8;;E8:E108)))}
What I want to do is this: I have a workbook that has around 100 sheets (all
of these sheets are equally arranged), so i want to sum all the figures from
a determined cell from every sheet.
In the above example the C8;D8 contain the row and column numbers to
identify the cell i want to sum and the array E8:E108 has the name of the
sheets.
I don't know if the adress function works with arrays. If anyone knows how i
can do this, please help.

thanks

Array Fromula
 

Try...

=SUMPRODUCT(N(INDIRECT("'"&E8:E108&"'!"&ADDRESS(C8 ,D8))))

Hope this helps!

In article ,
"Pmxgs" wrote:

Hi,

does anyone know why this formila isn't working?
{=SUM(INDIRECT(ADDRESS(C8;D8;;E8:E108)))}
What I want to do is this: I have a workbook that has around 100 sheets (all
of these sheets are equally arranged), so i want to sum all the figures from
a determined cell from every sheet.
In the above example the C8;D8 contain the row and column numbers to
identify the cell i want to sum and the array E8:E108 has the name of the
sheets.
I don't know if the adress function works with arrays. If anyone knows how i
can do this, please help.

thanks

Array Fromula
 

Hi

Rather than trying to use an array formula, just create a new sheet
called First and another called Last.
Drag these sheets to positions where your 100 sheets to be added are
"sandwiched" between them with your summary sheet outside of this
sandwich.
On your Summary sheet
=SUM(First:Last!C8:D8)
You can hide First and Last if you wish
--
Regards

Roger Govier


"Pmxgs" wrote in message
...
Hi,

does anyone know why this formila isn't working?
{=SUM(INDIRECT(ADDRESS(C8;D8;;E8:E108)))}
What I want to do is this: I have a workbook that has around 100
sheets (all of these sheets are equally arranged), so i want to sum
all the figures from a determined cell from every sheet.
In the above example the C8;D8 contain the row and column numbers to
identify the cell i want to sum and the array E8:E108 has the name of
the sheets.
I don't know if the adress function works with arrays. If anyone knows
how i can do this, please help.

thanks

Array Fromula
 

Perhaps I wasn't very clear.
I can't use a 3d reference because i don't want to sum all of the sheets.
That's why i need a range where i can type the name of the sheets i want to
sum. In my example would be E8:E108.


"JMB" wrote in message
...
Perhaps a 3D reference?

=SUM(Sheet1:Sheet100!A1)

will sum cell A1 on Sheet1 through Sheet100 (change the beginning and
ending
sheet names to whatever yours are called).


"Pmxgs" wrote:

Hi,

does anyone know why this formila isn't working?
{=SUM(INDIRECT(ADDRESS(C8;D8;;E8:E108)))}
What I want to do is this: I have a workbook that has around 100 sheets
(all
of these sheets are equally arranged), so i want to sum all the figures
from
a determined cell from every sheet.
In the above example the C8;D8 contain the row and column numbers to
identify the cell i want to sum and the array E8:E108 has the name of the
sheets.
I don't know if the adress function works with arrays. If anyone knows
how i
can do this, please help.

thanks

Array Fromula
 

Thanks a lot Domenic. It works!
The problem was related to the way i wrote the indirect function and address
function. The way you wrote works fine.
But the sum function works ok, sumproduct it's not needed.
Thanks again.

"Domenic" wrote in message
...
Try...

=SUMPRODUCT(N(INDIRECT("'"&E8:E108&"'!"&ADDRESS(C8 ,D8))))

Hope this helps!

In article ,
"Pmxgs" wrote:

Hi,

does anyone know why this formila isn't working?
{=SUM(INDIRECT(ADDRESS(C8;D8;;E8:E108)))}
What I want to do is this: I have a workbook that has around 100 sheets
(all
of these sheets are equally arranged), so i want to sum all the figures
from
a determined cell from every sheet.
In the above example the C8;D8 contain the row and column numbers to
identify the cell i want to sum and the array E8:E108 has the name of the
sheets.
I don't know if the adress function works with arrays. If anyone knows
how i
can do this, please help.

thanks

Array Fromula
 

In article ,
"Pmxgs" wrote:

Thanks a lot Domenic. It works!

You're very welcome! Glad I could help!

The way you wrote works fine.
But the sum function works ok, sumproduct it's not needed.

Of course, using SUM instead of SUMPRODUCT will require the formula to
be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.