[libreoffice-users] 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: 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