Indirect Formula Causes #N/A Error

Hello all,

I am having an issue with the indirect formula. What I am trying to do is more complex, but I believe I have dug down to the root of my error.

This formula works wonderfully:

=SUMPRODUCT((April4!$F$2:F20001='Weekly Performance'!C4)*(April4!$J$2:J20001))

However, I receive the lovely "#N/A" error when I add in Indirect:

=SUMPRODUCT((INDIRECT("April4!$F$2:F19999")='Weekly Performance'!C4)*(April4!$J$2:J20000))

I appreciate any help or suggestions. Thanks

Steve

Edit: I can attach the workbook if needed.

hi Steve. not sure what are you trying to achieve. usually, people use it in a case where they might have several sheets like:
April4
May4
Jun4

so say i have "April4" in A2. then i would use:
=SUMPRODUCT((INDIRECT(A2&"!F2:F20001")='Weekly Performance'!C4)*(April4!$J$2:J20001))

the above would be equivalent to your first formula. but if i change A2 to "May4", then it becomes an equivalent of:
=SUMPRODUCT((May4!$F$2:F20001='Weekly Performance'!C4)*(April4!$J$2:J20001))

and why do you fix the $F$2 portion. are you going to copy down & make it F2:F20002?

Problem Spreadsheet.xlsxThe formulas are in Column I, I have highlighted it. Please let me know if you need any more info. Thanks!

Steve

..Edit: I can attach the workbook if needed.

Pls do it explaing what is your goal using INDIRECT in your formula.

Also check what results do you get if you use correctly the ranges..

=SUMPRODUCT((INDIRECT("April4!$F$2:F19999")='Weekly Performance'!C4)*(April4!$J$2:J20000))

Benishiryo,

Thanks for the reply. You are spot on about what I am trying to do. I have an input cell which changes each week. My first thought was to try the formula you gave me, but it gave me a #REF error. As did many other iterations that I tried.

Yes, I need to fix the $F$2 portion. This is a spreadsheet I recently inherited and am trying to cleanup.

Steve

if i'm not wrong:
=SUMPRODUCT((INDIRECT("'"&$O$12&"'!F2:F19998")='Weekly Performance'!C2)*(INDIRECT("'"&$O$12&"'!J2:J19998")))

it will still show a REF error because you don't have a May4 sheet. when you do, it should work fine

And thus the problem is solved. Thanks for the help. I can't believe I've been struggling over such an obvious solution..

Steve