Date: prev next · Thread: first prev next last
2011 Archives by date, by thread · List index


2011/12/11 Ian Whitfield <whitfield@telkomsa.net>:
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: 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


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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.