Hello Excel Community,

I have a sales forecast worksheet that contains business oppurtunities
entered as single deals (by row). These can routinely carry-over from month
to month poviding the deal is still alive. I simply re-forecast a prediction
from one month to the next as the deal progresses. Forecast potential is
shown as a percentage against the total deal size, in a range of 12 cells
(K:V, representing Jan - Dec).

I need to know which cell (or month) in that range represents the last cell
to conatin a forecasted month. For ex: if Jan, Feb, and Mar were forecasted
on a deal, and Mar was the month the deal closed, then 03 is the information
I need to see as a number on my worksheet. I dont always know when a deal
closes so this has to be a formula that looks at the last entry in a range.
(Hope this makes sense).

I appreciate any feedback.

Thanks everyone...
--
Regards...

Last entry in range A2:A500 would be

=INDEX(A2:A500,COUNTA(A2:A500))

this assume there are no blanks in-between, otherwise

=LOOKUP(2,1/(A2:A500<""),A2:A500)

if numeric entries only

=LOOKUP(9.99999999999999E+307,A:A)

if text entries only

=INDEX(A:A,MATCH(REPT("z",255),A:A))



--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"cincode5" wrote in message
...
Hello Excel Community,

I have a sales forecast worksheet that contains business oppurtunities
entered as single deals (by row). These can routinely carry-over from
month
to month poviding the deal is still alive. I simply re-forecast a
prediction
from one month to the next as the deal progresses. Forecast potential is
shown as a percentage against the total deal size, in a range of 12 cells
(K:V, representing Jan - Dec).

I need to know which cell (or month) in that range represents the last
cell
to conatin a forecasted month. For ex: if Jan, Feb, and Mar were
forecasted
on a deal, and Mar was the month the deal closed, then 03 is the
information
I need to see as a number on my worksheet. I dont always know when a deal
closes so this has to be a formula that looks at the last entry in a
range.
(Hope this makes sense).

I appreciate any feedback.

Thanks everyone...
--
Regards...

Peo,

This works perfectly... Thank you very much!
--
Regards...


"Peo Sjoblom" wrote:

Last entry in range A2:A500 would be

=INDEX(A2:A500,COUNTA(A2:A500))

this assume there are no blanks in-between, otherwise

=LOOKUP(2,1/(A2:A500<""),A2:A500)

if numeric entries only

=LOOKUP(9.99999999999999E+307,A:A)

if text entries only

=INDEX(A:A,MATCH(REPT("z",255),A:A))



--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"cincode5" wrote in message
...
Hello Excel Community,

I have a sales forecast worksheet that contains business oppurtunities
entered as single deals (by row). These can routinely carry-over from
month
to month poviding the deal is still alive. I simply re-forecast a
prediction
from one month to the next as the deal progresses. Forecast potential is
shown as a percentage against the total deal size, in a range of 12 cells
(K:V, representing Jan - Dec).

I need to know which cell (or month) in that range represents the last
cell
to conatin a forecasted month. For ex: if Jan, Feb, and Mar were
forecasted
on a deal, and Mar was the month the deal closed, then 03 is the
information
I need to see as a number on my worksheet. I dont always know when a deal
closes so this has to be a formula that looks at the last entry in a
range.
(Hope this makes sense).

I appreciate any feedback.

Thanks everyone...
--
Regards...