Sumproduct and indirect array

[excelhelp20817]

Hi. I have two arrays specified by the following formulas: Array A is "OFFSET($I$11,0,0,5)" and array B is "OFFSET(J11,0,0,5)". When i put in the formula "SUMPRODUCT(OFFSET($I$11,0,0,5),OFFSET(J11,0,0,5))" it works fine and I get a result.

What I want to do is something like Sumproduct(A1,B1), where A1 refers to array A and B1 refers to array B. I think I should be using indirect here (something like sumproduct(indirect(A1),indirect(B1)) where A1 = "OFFSET($I$11,0,0,5)" and B1 is "OFFSET(J11,0,0,5)", but that is giving me #Ref.

Does anyone have any ideas?

Thanks so much for your help.

[mikeTRON]

Please Login or Register  to view this content.

where A1=I11 and B1=J11

[zedbee1]

What's the result you're hoping to see if it works?
Can you attach a sample version of your workbook and type on what you hope to see?
Ta,

Z

[excelhelp20817]

Hi. Thank you for your reply but it is not what I am looking for. I am trying to replace the entire array call (Offset(a,a,a,a) with one cell reference. In my actual spreadsheet I have a super long formula (6-7 lines long) with many different references to different arrays. I am trying to simplify the formula by simplifying each Offset(a,a,a,a) to one cell reference.

Thanks.

[excelhelp20817]

I have attached a sample spreadsheet. I would be looking for "Formula 2" to have the same value as formula 1, but to refer to the arrays as cell references located outside the formula 2 formula.

Thanks.

[mikeTRON]

You cannot use offset inside the indirect but you can use variables for every piece of the offset using Indirect.

[excelhelp20817]

SOLUTION (in case anyone else is looking). What you need to do instead of using offset is convert the array into A1 notation (I think that is what it is called). For example, if I have "OFFSET(D18,0,0,5)" that is equivalent to "SUBSTITUTE(ADDRESS(1,COLUMN(D18),4),"1","")&ROW(D18)&":"&SUBSTITUTE(ADDRESS(1,COLUMN(D18),4),"1","")&ROW(D18)+5". If I put the second formula in a cell I get D18:D23, which can then be referenced from within an sumproduct(indirect(),indirect()).

Thanks to everyone for the ideas.