3D INDIRECT, INDEX, OFFSET as array function?

[cristobal03]

First post; I searched the forum and found threads that are pretty close but not quite right for what I'm trying to do.

I have a working function that is unnecessarily redundant, and I'm trying to convert it to an array function. Here is the raw:

=
SUM(
IF(NOT(ISERROR(OFFSET(INDEX(INDIRECT("'" & [COLOR=#0000ff]A3[/COLOR] & "'!5:5"),MATCH(summary_invoice_month,INDIRECT("'" & [COLOR=#0000ff]A3[/COLOR] & "'!5:5"),0)),row(INDIRECT("'" & [COLOR=#0000ff]A3[/COLOR] & "'!monthly_billable")) - 5,0))),OFFSET(INDEX(INDIRECT("'" & [COLOR=#0000ff]A3[/COLOR] & "'!5:5"),MATCH(summary_invoice_month,INDIRECT("'" & [COLOR=#0000ff]A3[/COLOR] & "'!5:5"),0)),row(INDIRECT("'" & [COLOR=#0000ff]A3 [/COLOR]& "'!monthly_billable")) - 5,0),0),
IF(NOT(ISERROR(OFFSET(INDEX(INDIRECT("'" & [COLOR=#ff0000]A4 [/COLOR]& "'!5:5"),MATCH(summary_invoice_month,INDIRECT("'" & [COLOR=#ff0000]A4 [/COLOR]& "'!5:5"),0)),row(INDIRECT("'" & [COLOR=#ff0000]A4 [/COLOR]& "'!monthly_billable")) - 5,0))),OFFSET(INDEX(INDIRECT("'" & [COLOR=#ff0000]A4 [/COLOR]& "'!5:5"),MATCH(summary_invoice_month,INDIRECT("'" & [COLOR=#ff0000]A4 [/COLOR]& "'!5:5"),0)),row(INDIRECT("'" & [COLOR=#ff0000]A4 [/COLOR]& "'!monthly_billable")) - 5,0),0),
IF(NOT(ISERROR(OFFSET(INDEX(INDIRECT("'" & [COLOR=#008000]A5 [/COLOR]& "'!5:5"),MATCH(summary_invoice_month,INDIRECT("'" & [COLOR=#008000]A5 [/COLOR]& "'!5:5"),0)),row(INDIRECT("'" & [COLOR=#008000]A5 [/COLOR]& "'!monthly_billable")) - 5,0))),OFFSET(INDEX(INDIRECT("'" & [COLOR=#008000]A5 [/COLOR]& "'!5:5"),MATCH(summary_invoice_month,INDIRECT("'" & [COLOR=#008000]A5 [/COLOR]& "'!5:5"),0)),row(INDIRECT("'" & [COLOR=#008000]A5 [/COLOR]& "'!monthly_billable")) - 5,0),0),
IF(NOT(ISERROR(OFFSET(INDEX(INDIRECT("'" & [COLOR=#800080]A6 [/COLOR]& "'!5:5"),MATCH(summary_invoice_month,INDIRECT("'" & [COLOR=#800080]A6 [/COLOR]& "'!5:5"),0)),row(INDIRECT("'" & [COLOR=#800080]A6 [/COLOR]& "'!monthly_billable")) - 5,0))),OFFSET(INDEX(INDIRECT("'" & [COLOR=#800080]A6 [/COLOR]& "'!5:5"),MATCH(summary_invoice_month,INDIRECT("'" & [COLOR=#800080]A6 [/COLOR]& "'!5:5"),0)),row(INDIRECT("'" & [COLOR=#800080]A6 [/COLOR]& "'!monthly_billable")) - 5,0),0),
IF(NOT(ISERROR(OFFSET(INDEX(INDIRECT("'" & [COLOR=#daa520]A7 [/COLOR]& "'!5:5"),MATCH(summary_invoice_month,INDIRECT("'" & [COLOR=#daa520]A7 [/COLOR]& "'!5:5"),0)),row(INDIRECT("'" & [COLOR=#daa520]A7 [/COLOR]& "'!monthly_billable")) - 5,0))),OFFSET(INDEX(INDIRECT("'" & [COLOR=#daa520]A7 [/COLOR]& "'!5:5"),MATCH(summary_invoice_month,INDIRECT("'" & [COLOR=#daa520]A7 [/COLOR]& "'!5:5"),0)),row(INDIRECT("'" & [COLOR=#daa520]A7 [/COLOR]& "'!monthly_billable")) - 5,0),0)
)

This is for an project management invoice tracking workbook (a contract burn rate calculator). Here's the basic idea:

• Summary sheet that lists all the contracted vendors; contract value; spend to date; remaining; and estimated depletion
  • Summary sheet also has a helper field where I can put in a month (summary_invoice_month) and see the total billable for that month--this is where the above function fits in
• One sheet for each contract
  • Summary info
  • Table for itemized contract resources (one row for each resource, w/ rate; total hours; billed hours; remaining hours; and monthly tally)
  • Table for itemized resource costs (monthly hours * rate) and total monthly billable (monthly_billable)
• Additional relevant details
  • A3:A7 on the summary sheet is actually a named range called agency_order_nos
  • Row 5 in contract sheets contains month values as column headers, i.e. 5:5 is a series of months starting from contract execution to contract fulfillment--the monthly range will not be identical from one sheet to the next (thus the ISERROR in the conditional)
  • The bit about 'row(monthly_billable) - 5' is to account for differences between the number of line items from one sheet to the next, since I know row 5 is the starting point (the month headers)

Hopefully that all makes sense. I think it must be possible to do this as an array formula so I don't need to repeat the same expression five times for A3 through A7. I also think it probably isn't necessary to run the same expression in a conditional check and then again in the true part.

Help? Thanks!

chris