Array formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pmxgs - 01 Nov 2006 02:06 GMT

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

Reply to this Message

Lori - 01 Nov 2006 09:44 GMT

Try N(INDIRECT(...)) inside the sum. The indirect and offset return
arrays of references when their arguments contain arrays which need to
be converted to arrays of values for use in formulas. Functions that
take arrays of references and return arrays of values in this way
include: n,t,subtotal,sumif,countif,database and is functions.

> Hi,
>
[quoted text clipped - 10 lines]
>
> thanks

Reply to this Message

Roger Govier - 01 Nov 2006 10:57 GMT

see response in worksheet.functions

Signature

Regards

Roger Govier

> Hi,
>
[quoted text clipped - 14 lines]
>
> thanks

Reply to this Message