I have a cell reference that varies. I tried to use the indirect function, but I keep receiving error message as volatile. Pl help!
This is my original formula (this formula is at worksheet2, worksheet1 is supposed to be left untouched as it is a copied worksheet from another workbook):
=SUMPRODUCT((worksheet1!A22:A2000<>"")/COUNTIF(worksheet1!A22:A2000,worksheet1!A22:A2000&""))-1
Now that A22 varies, depending on a cell value at worksheet1A$19. I tried using the indirect function:
Worksheet2!B12 = 22+A19 (number field)
=SUMPRODUCT((INDIRECT("worksheet1!A"&worksheet2!B12&":A2000")<>"")/COUNTIF(INDIRECT("worksheet1!A"&worksheet2!B12&":A2000"),INDIRECT("worksheet1!A"&worksheet2!B12&":A2000")&""))-1

This is my original formula (this formula is at worksheet2, worksheet1 is supposed to be left untouched as it is a copied worksheet from another workbook):
=SUMPRODUCT((worksheet1!A22:A2000<>"")/COUNTIF(worksheet1!A22:A2000,worksheet1!A22:A2000&""))-1
Now that A22 varies, depending on a cell value at worksheet1A$19. I tried using the indirect function:
Worksheet2!B12 = 22+A19 (number field)
=SUMPRODUCT((INDIRECT("worksheet1!A"&worksheet2!B12&":A2000")<>"")/COUNTIF(INDIRECT("worksheet1!A"&worksheet2!B12&":A2000"),INDIRECT("worksheet1!A"&worksheet2!B12&":A2000")&""))-1