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

classic Classic list List threaded Threaded
3 messages Options
Ian Whitfield Ian Whitfield
Reply | Threaded
Open this post in threaded view
|

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

Hi All

Thanks for all the help offered by the Group - It's very much
appreciated!! And with this help - although I never got an exact answer
- I have been able to solve it!!

Basically my question could be broken down into three parts....

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?

After a lot of trial and error - and with the pointers given to me by
the Group I was able to 'crack' it!!

1) For the ROW NUMBER I used MATCH. At first this did not give me what I
wanted as MATCH returns the ROW COUNT within a specified Array and NOT
the actual ROW NUMBER! Then it occurred to me that if I make the Array
the whole portion of the spreadsheet where the numbers are, starting at
Row 1, and not just there actual position of the values I wanted to
check in then the ROW COUNT returned by MATCH would in fact be the
actual ROW NUMBER!! Problem solved, so

"=MATCH(Unique Number,Array)"
Where - 'Unique Number' is a value I put in a fixed cell so I use this
Cell address and the 'Array' is the whole section of the Spreadsheet
where these numbers are, (ie I have the numbers 1 to 12 in positions A7
to A18 so for the Array I specify *A1*:A18 so that the ROW COUNT matches
the ROW NUMBER).

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)"

Perfect - PROBLEM SOLVED!!

I can now change ONE VALUE in a cell (ie this month I'm working with
December figures so I enter "12" into the cell), and ALL my values
change automatically for that month. No more endless Cutting and Pasting!!

Thanks again guys - I hope this explanation will be of interest to you
and maybe of use to other users trying to automate yearly figures to do
monthly analysis.

Ian Whitfield.

--
For unsubscribe instructions e-mail to: [hidden email]
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

Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

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

2011/12/11 Ian Whitfield <[hidden email]>:

> Hi All
>
> Thanks for all the help offered by the Group - It's very much appreciated!!
> And with this help - although I never got an exact answer - I have been able
> to solve it!!
>
> Basically my question could be broken down into three parts....
>
> 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?
>
> After a lot of trial and error - and with the pointers given to me by the
> Group I was able to 'crack' it!!
>
> 1) For the ROW NUMBER I used MATCH. At first this did not give me what I
> wanted as MATCH returns the ROW COUNT within a specified Array and NOT the
> actual ROW NUMBER! Then it occurred to me that if I make the Array the whole
> portion of the spreadsheet where the numbers are, starting at Row 1, and not
> just there actual position of the values I wanted to check in then the ROW
> COUNT returned by MATCH would in fact be the actual ROW NUMBER!!

Or you could just use the original array and add what's missing, like
”=MATCH(Paramters)+Something”


Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

> Problem
> solved, so
>
> "=MATCH(Unique Number,Array)"
> Where - 'Unique Number' is a value I put in a fixed cell so I use this Cell
> address and the 'Array' is the whole section of the Spreadsheet where these
> numbers are, (ie I have the numbers 1 to 12 in positions A7 to A18 so for
> the Array I specify *A1*:A18 so that the ROW COUNT matches the ROW NUMBER).
>
> 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)"
>
> Perfect - PROBLEM SOLVED!!
>
> I can now change ONE VALUE in a cell (ie this month I'm working with
> December figures so I enter "12" into the cell), and ALL my values change
> automatically for that month. No more endless Cutting and Pasting!!
>
> Thanks again guys - I hope this explanation will be of interest to you and
> maybe of use to other users trying to automate yearly figures to do monthly
> analysis.
>
> Ian Whitfield.
>
>
> --
> For unsubscribe instructions e-mail to: [hidden email]
> 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
>

--
For unsubscribe instructions e-mail to: [hidden email]
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
Jack Jack
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Ian Whitfield
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: [hidden email]
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