My curiosity pushes me to ask this question.

If I were to have a series of functions that referenced a different column in a worksheet but all ended on the same row of data is there a way to point the 'row' part of a cell reference to a blank cell and use it has a variable to show the results of the functions up to a desired row simultaneously?

Example:

=Average('worksheet 1'.$A$1:'worksheet 1'.$A100)

=Max('worksheet 1'.$B$1:'worksheet 1'.$B100)

=Min('worksheet 1'.$C$1:'worksheet 1'.$C100)

=Sum('worksheet 1'.$D$1:'worksheet 1'.$D100)

Pseudo formulas... =Average('worksheet 1'.$A$1:'worksheet 1'.$A*('worksheet 2'.$A$1)*)

=Max('worksheet 1'.$B$1:'worksheet 1'.$B*('worksheet 2'.$A$1)*)

=Min('worksheet 1'.$C$1:'worksheet 1'.$C*('worksheet 2'.$A$1)*)

=Sum('worksheet 1'.$D$1:'worksheet 1'.$D*('worksheet 2'.$A$1)*)

Where 'worksheet 2'.$A$1 would only contain a number corresponding to a row in 'worksheet 1'.

After stumbling upon and playing with the indirect() function I have only been able to replace the entire cell reference (Column and Row) with any success.

The formula so far =SUM('worksheet 1'.C3:INDIRECT(A1))

Where A1 is on 'worksheet 2' and contains a full cell reference pointing to 'worksheet 1'.

Any pointers?