Re: [libreoffice-users] Re: [SOLVED] Identifying the correct row in Spreadsheet formulas

Ian Whitfield wrote:
> 1) How do I work out what ROW NUMBER a given unique value is on?
> 2) How do I use this ROW NUMBER to make-up a valid CELL ADDRESS?
> 3) How do I get the VALUE in that CELL ADDRESS?
> [...]
> 2) For the CELL ADDRESS I have a cell that identifies each COLUMN that these
> values resides in (ie all my TOTALS are in COLUMN H), and I now have the ROW
> COUNT of the data I'm looking for. So I can use
>
> "=COLUMN ID & fixed ROW NUMBER"
>
> 3) The last step is to get the VALUE that is in the Cell at that location.
> For this I used
>
> "=INDIRECT (CELL ADDRESS)"

I prefer using OFFSET(), rather than INDIRECT().  Here's an example:

Column A contains the name of fruits, column B contains the quantity.
Let's say A2 contains "Apples", B2 contains "12", A3 contains "Pears"
and B3 contains "7".  If I want to know how many "Pears" I have, I
would use something like this:
= OFFSET(A2, MATCH("Pears", A2:A3, 0) - 1, 1)

The result would be "7" (the value in the second column).

Obviously, you can replace "Pears" with "Apples" or a cell reference.

> Perfect - PROBLEM SOLVED!!

Ok then.  Just thought I'd give you another suggestion.

-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted