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

 

[Aladin Akyurek]

Hi
I received error message "too many arguments", not really sure on how to use the frequency function. Pl help

Sorry about that...

1. Just enter in say C12 of wokshhet2...

=SUMPRODUCT((INDEX(worksheet1!A:A,worksheet2!B12):worksheet1!A2000<>"")/COUNTIF(INDEX(worksheet1!A:A,worksheet2!B12):worksheet1!A2000,INDEX(worksheet1!A:A,worksheet2!B12):worksheet1!A2000&""))-1

2. Control+shift+enter in say C12 of wokshhet2...

=SUM(IF(FREQUENCY(IF(1-(INDEX(worksheet1!A:A,B12):worksheet1!A2000=""),MATCH(INDEX(worksheet1!A:A,B12):worksheet1!A2000,INDEX(worksheet1!A:A,B12):worksheet1!A2000,0)),MATCH(INDEX(worksheet1!A:A,B12):worksheet1!A2000,INDEX( worksheet1!A:A,B12):worksheet1!A2000,0)),1))-1