Den søndag den 5. oktober 2014 13.29.00 UTC+2 skrev Claus Busch:
Hi Jan,



Am Sun, 5 Oct 2014 04:02:10 -0700 (PDT) schrieb Jan Kronsell:



In a workbook I have 36 sheets, one for each month from January 2012 to December 2014, named Ja12, Fe12, Ma12...No14, De14.



All sheets are alike. In Column A (A5:A20) I have customer numbers, In column Q (Q5:Q20) I have sales totals.



In an additional sheet, I like to be able to type a customer number in D9, and in F9 have returned the total sale for te customer over the 36 months. In this sheet, column A I have a list of all my 36 sheets.



write in this additional sheet from A1 to A36 all the sheet names.

If your customer number is a real number then try:

=SUM((N(INDIRECT("'"&TRANSPOSE(A1:A36)&"'!A"&ROW(5 :20)))=D9)*(N(INDIRECT("'"&TRANSPOSE(A1:A36)&"'!Q" &ROW(5:20)))))

If the customer number is text then try:

=SUM((T(INDIRECT("'"&TRANSPOSE(A1:A36)&"'!A"&ROW(5 :20)))=D9)*(N(INDIRECT("'"&TRANSPOSE(A1:A36)&"'!Q" &ROW(5:20)))))



Both formulas are array formulas to insert with CTRL+Shift+Enter




I tried your formula but get a #NAME! error.

Jan