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