# How flexible is the 'indirect' function?

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?

-
Hello Chuck, and welcome to SuperUser! I removed the ubuntu tag on your question because it is not important for the answer. For instance, the answer to this question will be useful to people using LibreOffice on Windows and Macs. We try to tag questions as broadly as possible. Please keep this in mind the next time you ask a question. – MBraedley Jul 7 '12 at 21:08
Don't put "solved" into the question, you can mark your answer as accepted using the green checkmark. Thanks! – slhck Jul 19 '12 at 2:05
Thanks for the correction slhck. – Chuck Jul 25 '12 at 19:09

``````Concatenate()
I had an epiphany today and after looking around the function wizard found that spreadsheets have a concatenate function. A nested `INDIRECT(CONCATENATE())` did the trick. The Concatenate function can be filled with cell references (which I can modify manually at whim) and the Indirect function uses the combined items to point to a position on another spread sheet like so.
``````LOOKUP(\$A6,\$'Receipts - Labor'.\$A\$3:INDIRECT(CONCATENATE("'Receipts - Labor'.A",\$D\$1)),\$'Receipts - Labor'.C\$3:INDIRECT(CONCATENATE("'Receipts - Labor'.C",\$D\$1)))
The absolute cell reference `\$D\$1` allows me to increase the upper range for all formulae with by modifying a single cell.