find last cell that has a value in row

[Jez]

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]

=OFFSET(AI3,0,COUNT(AI3:CH3)-1)

presuming that you have no blank weeks

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

[Domenic]

For the last numerical value, try...

=LOOKUP(9.99999999999999E+307,AI3:CH3)

Hope this helps!

In article <[email protected]>,
Jez <[email protected]> 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

[Jez]

Thankyou, this works a treat.

"Domenic" wrote:

> For the last numerical value, try...
>
> =LOOKUP(9.99999999999999E+307,AI3:CH3)
>
> Hope this helps!
>
> In article <[email protected]>,
> Jez <[email protected]> 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
>

[Jez]

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
>
>