3d array formula

[oaishm]

I have this formula, which works quite nicely

sum('usd begin:usd end'!m10)

to automate it, I started setting local names in the spreadsheet so I can use the sheet more than once.

so sheet1!currency=sheet1!$b$3 for example.

So in sheet 1, a made this formula

=sum(indirect("'"&currency&" begin:"&currency&" end'!"&CELL("address",M10)))

alas, I have a ref error. I checked the string and it was fine. Any suggestions?

 

[Domenic]

Here's a possible workaround... First, create a table listing the currencies along with their respective sheet names. So, for example...

Let E2 contain your first currency, such as the USD, and E3:E10 the corresponding sheet names.

Let F2 contain your second currency, and F3:F10 the corresponding sheet names.

Let G2 contain your third currency, and G3:G10 the corresponding sheet names.

Then define the following...

Insert > Name > Define

Name:  SheetList

Refers to:

=INDEX(Sheet1!$E$3:$G$10,1,MATCH(Currency,Sheet1!$E$2:$G$2,0)):INDEX(Sheet1!$E$3:$G$10,MATCH(REPT("z",255),INDEX(Sheet1!$E$3:$G$10,0,MATCH(Currency,Sheet1!$E$2:$G$2,0))),MATCH(Currency,Sheet1!$E$2:$G$2,0))

**Note:  If the sheet names are numerical values, replace REPT("z",255) with 9.99999999999999E+307.

Click Ok

Then try...

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!"&CELL("address",M10)),"<>"))

Add to your table and adjust the references, accordingly.