You're very welcome :-)

--
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
...
Thank you Ken,

I did a test with the example ranges and now I understand how it works.

This will help tremendously, in my job.

Sincere thanks, Have a Great Day!


"Ken Wright" wrote:

Probably my fault, I just reread your message and am wondering if the
data
you have in D1 is actually a real date or not. I'm guessing now that it
isn't.

What the OFFSET formula does in this instance is to take a starting
point,
and then move away from that cell by x number of columns. The x is
determined by the Month number,eg

If D1 = Jan, then Month(D1)=1, likewise if D1 = Feb, then Month(D1)=2
etc,
AS LONG as the value in D1 is a valid date

D1=Jan= 1 == =OFFSET($A$10,,MONTH(D1)) = B10
D1=Feb= 2 == =OFFSET($A$10,,MONTH(D1)) = C10
D1=Mar= 3 == =OFFSET($A$10,,MONTH(D1)) = D10
D1=Apr= 4 == =OFFSET($A$10,,MONTH(D1)) = E10
D1=May=5 == =OFFSET($A$10,,MONTH(D1)) = F10

If it is not a real date, then assuming it is a 3 letter abbreviation in
D1
representing the Month, then you can try:-


=OFFSET($A$10,,MATCH(D1,{"Jan","Feb","Mar","Apr"," May","Jun","Jul","Aug","Se
p","Oct","Nov","Dec"},0))

or if you really want the full month then adjust the formula above, or
you
can try


=OFFSET($A$10,,MATCH(LEFT(D1,3),{"Jan","Feb","Mar" ,"Apr","May","Jun","Jul","
Aug","Sep","Oct","Nov","Dec"},0))

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

Thank you for the reply, its nice and short, however can you shed a
little
more information on the formula? I tried this and I came up with a
"value".

The month, payments and the cell that will hold the formual are all on
difference worksheets in a workbook. Do they need to be next to each
other?

I assume that the range( $A$10) that will return the payment amount
should

also include the headings for prior month date?

I'm not exactly sure how the data must be positioned to get the
desired
results.

I'm sure this will work for me I just need to set it up properly. Can
you
give me of an example of this formula?

Thank you so much for your help.

Debbie



"Ken Wright" wrote:

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