2011/12/11 Ian Whitfield <whitfi...@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+h...@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+h...@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
