I am looking to find out if there is a way to do this:

I have a vector of values say (D1:D9) witch equal different weeks. I fill
in a cell each week starting with D1, and after 9 weeks I fill in D9. I need
to create a formula that will find the furthest cell to the right to make my
solution accurate.

Basically, I have a constant number witch is integrated into the formula,
but my variable will change... and hopefully automatically based on it being
the furthest cell to the right or whatever the technical way of saying it
would be.

Thanks for your help!