Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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

1 Answer 1

up vote 1 down vote accepted
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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.