Indirect Function

[ElaineMok]

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

[macropod]

Cross-posted at: https://www.mrexcel.com/forum/excel-...-function.html
Please read Excel Forum's Cross-Posting policy in rule 8: http://www.excelforum.com/forum-rule...rum-rules.html