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]

Apparently, we have a range in worksheet1, which is currently A22:A2000. This must be adjusted as n:A2000 where n is specified somewhere else. Where exactly?

 

[ELAINEMOK]

This n is now
Indirect(worksheet1!A"&worksheet2!B12&":A2000")

 

[Aladin Akyurek]

This n is now
Indirect(worksheet1!A"&worksheet2!B12&":A2000")

You mean B12 of worksheet2 specifies the row for worksheet1!An:A2000. If B12 is 19 for example, we must have worksheet1!A19:A200, right?

 

[ELAINEMOK]

Yes

 

[Aladin Akyurek]

Yes

One more question:

=SUMPRODUCT((worksheet1!A22:A2000<>"")/COUNTIF(worksheet1!A22:A2000,worksheet1!A22:A2000&""))-1

does a unique count. Why -1?

 

[Macropod]

Cross-posted at: https://www.excelforum.com/excel-formulas-and-functions/1202462-indirect-function.html
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[ELAINEMOK]

-1 because it happens that I need to subtract one from the unique count

 

[Aladin Akyurek]

-1 because it happens that I need to subtract one from the unique count

I was curious why...

1. Assuming that the formula must be located in worksheet2...

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

2. The following does the same, but faster. Control+shift+enter, not just enter:

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

 

[ELAINEMOK]

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