Re: [libreoffice-users] Calk - lookup cell in column
The closest I came to a solution was a scenario where all the dates for the year were in one column. I put the dates for the year (Monday 12/31/2012 thru Sunday 1/5/2014 in A5:A375. Then, in A1 I put whatever date I'm looking for. Off to the side (D1) I entered: =VLOOKUP($A$1,$A$5:$A$375,1,0)-(A5-1)+4 VLOOKUP(A1,$A$5:$A$375,1,1) finds the date you're looking for. -(A5-1) subtracts the day before the first date on the list FROM the date found. +4 adds in the four rows above the range. The result is the row number of the date you're looking for. I hope this helps. -- Tim === On 9/13/2013 11:36 AM, IGraham wrote: Hi Now this might take a bit of explaining I have a spreadsheet that records data for each day of the year Vertically row by row I have the days of the year I like my week to start on a Monday So I have my year/days set to start on the first Monday of the year So the actual first day of the year position/row is dynamic either on the Monday or above This all works ok However I then (for manipulating data by month) need to know the row/cell in which the first day of the year (or month) appears How do I look up/find within a column the cell in which the first day of the month is - I assume that if I know how to do it for say January then I can do it for any month. I'm totally clueless as to how to do this /Hopping that my explanation isn't to bamboozling/ Any ideas - please - IGraham W764 LibreOffice 4.1.0.4 -- View this message in context: http://nabble.documentfoundation.org/Calk-lookup-cell-in-column-tp4074117.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Calk - lookup cell in column
On 09/14/2013 07:01 PM, Tim Deaton wrote: The closest I came to a solution was a scenario where all the dates for the year were in one column. I put the dates for the year (Monday 12/31/2012 thru Sunday 1/5/2014 in A5:A375. Then, in A1 I put whatever date I'm looking for. Off to the side (D1) I entered: =VLOOKUP($A$1,$A$5:$A$375,1,0)-(A5-1)+4 VLOOKUP(A1,$A$5:$A$375,1,1) finds the date you're looking for. -(A5-1) subtracts the day before the first date on the list FROM the date found. +4 adds in the four rows above the range. The result is the row number of the date you're looking for. A simpler formula: =MATCH($a$1,$a$5:$a$375,0)+row($a$5)-1 MATCH returns the offset into the array (so if $a$1 = $a$5, MATCH would return 1), and row($a$5)-1 adjusts it to give the absolute row. The reason I do it this way is that if you name the array (e.g. name it YearRange), it works as follows: =MATCH($a$1,YearRange,0)+row(YearRange)-1 So you don't need to know what row your array starts on to write the formula. Jim -- To unsubscribe e-mail to: users+unsubscr...@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
[libreoffice-users] Calk - lookup cell in column
Hi Now this might take a bit of explaining I have a spreadsheet that records data for each day of the year Vertically row by row I have the days of the year I like my week to start on a Monday So I have my year/days set to start on the first Monday of the year So the actual first day of the year position/row is dynamic either on the Monday or above This all works ok However I then (for manipulating data by month) need to know the row/cell in which the first day of the year (or month) appears How do I look up/find within a column the cell in which the first day of the month is - I assume that if I know how to do it for say January then I can do it for any month. I'm totally clueless as to how to do this /Hopping that my explanation isn't to bamboozling/ Any ideas - please - IGraham W764 LibreOffice 4.1.0.4 -- View this message in context: http://nabble.documentfoundation.org/Calk-lookup-cell-in-column-tp4074117.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@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
