How flexible is the 'indirect' function?
- by Chuck
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?
