find last cell that has a value in row
I have row AI3:CH3 with data added each week. For example after 4 weeks,
AI3:AL3 has data and the rest of the row is blank. Each week, I add a value in the next cell to the right of the previous week, so by the end of the year, I will have 52 values filling AI3:CH3. What formula will look at this row, and display the "right hand most value" ignoring all the blank cells in the row. IE I need to have displayed the value each week, for the week prior (in another worksheet) Thanks |
=OFFSET(AI3,0,COUNT(AI3:CH3)-1) presuming that you have no blank weeks Jez Wrote: I have row AI3:CH3 with data added each week. For example after 4 weeks, AI3:AL3 has data and the rest of the row is blank. Each week, I add a value in the next cell to the right of the previous week, so by the end of the year, I will have 52 values filling AI3:CH3. What formula will look at this row, and display the "right hand most value" ignoring all the blank cells in the row. IE I need to have displayed the value each week, for the week prior (in another worksheet) Thanks -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=473701 |
For the last numerical value, try...
=LOOKUP(9.99999999999999E+307,AI3:CH3) Hope this helps! In article , Jez wrote: I have row AI3:CH3 with data added each week. For example after 4 weeks, AI3:AL3 has data and the rest of the row is blank. Each week, I add a value in the next cell to the right of the previous week, so by the end of the year, I will have 52 values filling AI3:CH3. What formula will look at this row, and display the "right hand most value" ignoring all the blank cells in the row. IE I need to have displayed the value each week, for the week prior (in another worksheet) Thanks |
Thankyou, this works a treat.
"Domenic" wrote: For the last numerical value, try... =LOOKUP(9.99999999999999E+307,AI3:CH3) Hope this helps! In article , Jez wrote: I have row AI3:CH3 with data added each week. For example after 4 weeks, AI3:AL3 has data and the rest of the row is blank. Each week, I add a value in the next cell to the right of the previous week, so by the end of the year, I will have 52 values filling AI3:CH3. What formula will look at this row, and display the "right hand most value" ignoring all the blank cells in the row. IE I need to have displayed the value each week, for the week prior (in another worksheet) Thanks |
Thanks Bryan, this also works, but as you said, provided there is no blank
weeks. if the value for the week is zero, I will put in '0' rather than leaving blank. Cheers everyone. "Bryan Hessey" wrote: =OFFSET(AI3,0,COUNT(AI3:CH3)-1) presuming that you have no blank weeks Jez Wrote: I have row AI3:CH3 with data added each week. For example after 4 weeks, AI3:AL3 has data and the rest of the row is blank. Each week, I add a value in the next cell to the right of the previous week, so by the end of the year, I will have 52 values filling AI3:CH3. What formula will look at this row, and display the "right hand most value" ignoring all the blank cells in the row. IE I need to have displayed the value each week, for the week prior (in another worksheet) Thanks -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=473701 |
All times are GMT +1. The time now is 01:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com