View Single Post
  #2   Report Post  
Ken Wright
 
Posts: n/a
Default

=OFFSET($A$10,,MONTH(D1))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"taxmom" wrote in message
...
Hi,

The previous answer from Michael was great. However, I realized that I

will
have more than 7 nested If statements.
I didn't need the year so, I took it out of the equation.

Is there a way to streamline the formula for 12 months?

the previous answer =if(and(month(d1)=1,B10,if(and(month(d1)=2,C10,0))

cell: D1 will change from January to December (each months return)
Cells: B10, C10, E10, D10, etc for 12 months - contains prepayment

amounts.

As the months change I need the formula to look at the current date and
return the previous months prepayment amount.
There is a formula from the current return that fills this schedule on a
monthly basis.

The prepayment schedule looks like this:

location dec jan feb etc
1 boca raton 5,000 6,000 7,000
2 St John 2,000 1,000 7,000
etc.

The formula will be individual for each location return.
Should I use a lookup and range name the location and date?

Any suggestions?