I"ve asked this question before and didn't get an answer, so I thought I'd
phrase it differently. Maybe it isn't possible? If I can't get it to work in
VBA, then even if it has to be in regular formulas that I can run a macro on
that's okay. I would really appreciate some help.

I have a spreadsheet that has formulas that change based on the date that's
input into cell B1. Right now I'm up to this formula and I'm only 5 months
into the If's (that's Nov 08). I have to go to Dec 2010:

=IF(($D$16=$B$1),B13,IF(($D$17=$B$1),B12,IF(($D$18 =$B$1),B11,IF(($D$19=$B$1),
B10,IF((B20=B1),(E18-$H$4),B13)))))

If it helps I've made a recreation of the spreadsheet below (not pretty, but
copying and pasting into the forum messed it up a little). The rows are
numbered on the left, with the Column letters on the top. I also put what the
formulas were before I started trying to nest the formulas.


A B C D E
1 31-Jul
2
3
4 Period Value Formula
in col E Bef Ifs
5 Aug-07 $18,848 (no formula
here)
6 Sep-07 $18,320 'E5-
$H$4
7 Oct-07 $17,792 'E6-
$H$4
8 Nov-07 $17,264 'E7-
$H$4
9 Term Value Dec-07 $16,736 'E8-$H$4
10 1 $13,905 Jan-08 $16,208 'E9-$H$4
11 2 $12,920 Feb-08 $15,680 'E10-$H$4
12 3 $12,015 Mar-08 $15,152 'E11-$H
$4
13 4 $11,455 Apr-08 $14,623 'E12-$H
$4
14 5 $11,095 May-08 $14,095 'E13-$H
$4
15 6 $11,060 Jun-08 $13,567 'E14-$H
$4
16 7 $11,200 Jul-08 $13,039 'E15-
$H$4
17 8 $11,010 Aug-08 $13,905 'B10
18 9 $10,580 Sep-08 $12,920 'B11
19 10 $10,160 Oct-08 $12,015 'B12
20 11 $9,705 Nov-08 $11,455 'B13
21 12 $9,210 Dec-08 $11,095 'B14
22 13 $8,665 Jan-09 $11,060 'B15
23 14 $7,985 Feb-09 $11,200 'B16

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200807/1

Lonnie,

You need to describe, in words, what it is that you want to do. Your table doesn't make immediate
sense (what is in H4?), so it is impossible to decipher just what it is that you want to do.

HTH,
Bernie
MS Excel MVP


"lonnierudd via OfficeKB.com" <u11209@uwe wrote in message news:87d438f81f8af@uwe...
I"ve asked this question before and didn't get an answer, so I thought I'd
phrase it differently. Maybe it isn't possible? If I can't get it to work in
VBA, then even if it has to be in regular formulas that I can run a macro on
that's okay. I would really appreciate some help.

I have a spreadsheet that has formulas that change based on the date that's
input into cell B1. Right now I'm up to this formula and I'm only 5 months
into the If's (that's Nov 08). I have to go to Dec 2010:

=IF(($D$16=$B$1),B13,IF(($D$17=$B$1),B12,IF(($D$18 =$B$1),B11,IF(($D$19=$B$1),
B10,IF((B20=B1),(E18-$H$4),B13)))))

If it helps I've made a recreation of the spreadsheet below (not pretty, but
copying and pasting into the forum messed it up a little). The rows are
numbered on the left, with the Column letters on the top. I also put what the
formulas were before I started trying to nest the formulas.


A B C D E
1 31-Jul
2
3
4 Period Value Formula
in col E Bef Ifs
5 Aug-07 $18,848 (no formula
here)
6 Sep-07 $18,320 'E5-
$H$4
7 Oct-07 $17,792 'E6-
$H$4
8 Nov-07 $17,264 'E7-
$H$4
9 Term Value Dec-07 $16,736 'E8-$H$4
10 1 $13,905 Jan-08 $16,208 'E9-$H$4
11 2 $12,920 Feb-08 $15,680 'E10-$H$4
12 3 $12,015 Mar-08 $15,152 'E11-$H
$4
13 4 $11,455 Apr-08 $14,623 'E12-$H
$4
14 5 $11,095 May-08 $14,095 'E13-$H
$4
15 6 $11,060 Jun-08 $13,567 'E14-$H
$4
16 7 $11,200 Jul-08 $13,039 'E15-
$H$4
17 8 $11,010 Aug-08 $13,905 'B10
18 9 $10,580 Sep-08 $12,920 'B11
19 10 $10,160 Oct-08 $12,015 'B12
20 11 $9,705 Nov-08 $11,455 'B13
21 12 $9,210 Dec-08 $11,095 'B14
22 13 $8,665 Jan-09 $11,060 'B15
23 14 $7,985 Feb-09 $11,200 'B16

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200807/1

I'm not 100% sure I got this right, but does this formula placed in E6 and
copied down do what you want?

=IF(C6="","",IF(C6<=B$1,E5-$H$4,INDEX(B$10:B$1000,1+DATEDIF(B$1,C6,"m"))))

Where I am assuming you update the date in B1 monthly. By the way, if that
is what you are doing (and if the formula does what you want), you can
replace the two occurrences of B$1 with TODAY() and the formula should work
without you having to update B1 on a monthly basis.

Rick


"lonnierudd via OfficeKB.com" <u11209@uwe wrote in message
news:87d438f81f8af@uwe...
I"ve asked this question before and didn't get an answer, so I thought I'd
phrase it differently. Maybe it isn't possible? If I can't get it to work
in
VBA, then even if it has to be in regular formulas that I can run a macro
on
that's okay. I would really appreciate some help.

I have a spreadsheet that has formulas that change based on the date
that's
input into cell B1. Right now I'm up to this formula and I'm only 5 months
into the If's (that's Nov 08). I have to go to Dec 2010:

=IF(($D$16=$B$1),B13,IF(($D$17=$B$1),B12,IF(($D$18 =$B$1),B11,IF(($D$19=$B$1),
B10,IF((B20=B1),(E18-$H$4),B13)))))

If it helps I've made a recreation of the spreadsheet below (not pretty,
but
copying and pasting into the forum messed it up a little). The rows are
numbered on the left, with the Column letters on the top. I also put what
the
formulas were before I started trying to nest the formulas.


A B C D E
1 31-Jul
2
3
4 Period Value
Formula
in col E Bef Ifs
5 Aug-07 $18,848 (no
formula
here)
6 Sep-07 $18,320
'E5-
$H$4
7 Oct-07 $17,792
'E6-
$H$4
8 Nov-07 $17,264
'E7-
$H$4
9 Term Value Dec-07 $16,736 'E8-$H$4
10 1 $13,905 Jan-08 $16,208
'E9-$H$4
11 2 $12,920 Feb-08 $15,680
'E10-$H$4
12 3 $12,015 Mar-08 $15,152
'E11-$H
$4
13 4 $11,455 Apr-08 $14,623
'E12-$H
$4
14 5 $11,095 May-08 $14,095
'E13-$H
$4
15 6 $11,060 Jun-08 $13,567
'E14-$H
$4
16 7 $11,200 Jul-08 $13,039
'E15-
$H$4
17 8 $11,010 Aug-08 $13,905
'B10
18 9 $10,580 Sep-08 $12,920
'B11
19 10 $10,160 Oct-08 $12,015
'B12
20 11 $9,705 Nov-08 $11,455
'B13
21 12 $9,210 Dec-08 $11,095
'B14
22 13 $8,665 Jan-09 $11,060
'B15
23 14 $7,985 Feb-09 $11,200
'B16

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200807/1

=INDEX($B$2:$B$13,13-MATCH($B$1,$D$16:$D$27,0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"lonnierudd via OfficeKB.com" <u11209@uwe wrote in message
news:87d438f81f8af@uwe...
I"ve asked this question before and didn't get an answer, so I thought I'd
phrase it differently. Maybe it isn't possible? If I can't get it to work
in
VBA, then even if it has to be in regular formulas that I can run a macro
on
that's okay. I would really appreciate some help.

I have a spreadsheet that has formulas that change based on the date
that's
input into cell B1. Right now I'm up to this formula and I'm only 5 months
into the If's (that's Nov 08). I have to go to Dec 2010:

=IF(($D$16=$B$1),B13,IF(($D$17=$B$1),B12,IF(($D$18 =$B$1),B11,IF(($D$19=$B$1),
B10,IF((B20=B1),(E18-$H$4),B13)))))

If it helps I've made a recreation of the spreadsheet below (not pretty,
but
copying and pasting into the forum messed it up a little). The rows are
numbered on the left, with the Column letters on the top. I also put what
the
formulas were before I started trying to nest the formulas.


A B C D E
1 31-Jul
2
3
4 Period Value
Formula
in col E Bef Ifs
5 Aug-07 $18,848 (no
formula
here)
6 Sep-07 $18,320
'E5-
$H$4
7 Oct-07 $17,792
'E6-
$H$4
8 Nov-07 $17,264
'E7-
$H$4
9 Term Value Dec-07 $16,736 'E8-$H$4
10 1 $13,905 Jan-08 $16,208
'E9-$H$4
11 2 $12,920 Feb-08 $15,680
'E10-$H$4
12 3 $12,015 Mar-08 $15,152
'E11-$H
$4
13 4 $11,455 Apr-08 $14,623
'E12-$H
$4
14 5 $11,095 May-08 $14,095
'E13-$H
$4
15 6 $11,060 Jun-08 $13,567
'E14-$H
$4
16 7 $11,200 Jul-08 $13,039
'E15-
$H$4
17 8 $11,010 Aug-08 $13,905
'B10
18 9 $10,580 Sep-08 $12,920
'B11
19 10 $10,160 Oct-08 $12,015
'B12
20 11 $9,705 Nov-08 $11,455
'B13
21 12 $9,210 Dec-08 $11,095
'B14
22 13 $8,665 Jan-09 $11,060
'B15
23 14 $7,985 Feb-09 $11,200
'B16

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200807/1

Sorry, but I could have been more clear. Cell H4 is the difference in the
market value by month on the vehicle. On what I posted H4 is $528.07 for the
month of July, 2008. On the table in cell B1 is entered the last day of the
month we're looking at. This time it's July 31, 2008, so the formula needs to
be pulled down from the cell beside June 2008, relatively speaking. In June
the formula is E14-$H$4, so in July it's E15-$H$4. The month after the date
we're looking at needs to equal what is in cell B10, in this case that would
be what is beside August 2008. Also, the cells below that need to change also,
so that September equals B11, October equals B12, etc. Next month, the
formula next to August will be E16-$H$4, and September will equal B10,
October will equal B11, etc. Does that make sense?

Bernie Deitrick wrote:
Lonnie,

You need to describe, in words, what it is that you want to do. Your table doesn't make immediate
sense (what is in H4?), so it is impossible to decipher just what it is that you want to do.

HTH,
Bernie
MS Excel MVP

I"ve asked this question before and didn't get an answer, so I thought I'd
phrase it differently. Maybe it isn't possible? If I can't get it to work in
[quoted text clipped - 47 lines]
22 13 $8,665 Jan-09 $11,060 'B15
23 14 $7,985 Feb-09 $11,200 'B16

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200807/1

By the way, I have assumed that Column C contains *real* dates simply
formatted to look like you showed.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
I'm not 100% sure I got this right, but does this formula placed in E6 and
copied down do what you want?

=IF(C6="","",IF(C6<=B$1,E5-$H$4,INDEX(B$10:B$1000,1+DATEDIF(B$1,C6,"m"))))

Where I am assuming you update the date in B1 monthly. By the way, if that
is what you are doing (and if the formula does what you want), you can
replace the two occurrences of B$1 with TODAY() and the formula should
work without you having to update B1 on a monthly basis.

Rick


"lonnierudd via OfficeKB.com" <u11209@uwe wrote in message
news:87d438f81f8af@uwe...
I"ve asked this question before and didn't get an answer, so I thought
I'd
phrase it differently. Maybe it isn't possible? If I can't get it to work
in
VBA, then even if it has to be in regular formulas that I can run a macro
on
that's okay. I would really appreciate some help.

I have a spreadsheet that has formulas that change based on the date
that's
input into cell B1. Right now I'm up to this formula and I'm only 5
months
into the If's (that's Nov 08). I have to go to Dec 2010:

=IF(($D$16=$B$1),B13,IF(($D$17=$B$1),B12,IF(($D$18 =$B$1),B11,IF(($D$19=$B$1),
B10,IF((B20=B1),(E18-$H$4),B13)))))

If it helps I've made a recreation of the spreadsheet below (not pretty,
but
copying and pasting into the forum messed it up a little). The rows are
numbered on the left, with the Column letters on the top. I also put what
the
formulas were before I started trying to nest the formulas.


A B C D E
1 31-Jul
2
3
4 Period Value Formula
in col E Bef Ifs
5 Aug-07 $18,848 (no
formula
here)
6 Sep-07 $18,320 'E5-
$H$4
7 Oct-07 $17,792 'E6-
$H$4
8 Nov-07 $17,264 'E7-
$H$4
9 Term Value Dec-07 $16,736
'E8-$H$4
10 1 $13,905 Jan-08 $16,208 'E9-$H$4
11 2 $12,920 Feb-08 $15,680 'E10-$H$4
12 3 $12,015 Mar-08 $15,152 'E11-$H
$4
13 4 $11,455 Apr-08 $14,623 'E12-$H
$4
14 5 $11,095 May-08 $14,095 'E13-$H
$4
15 6 $11,060 Jun-08 $13,567 'E14-$H
$4
16 7 $11,200 Jul-08 $13,039 'E15-
$H$4
17 8 $11,010 Aug-08 $13,905 'B10
18 9 $10,580 Sep-08 $12,920 'B11
19 10 $10,160 Oct-08 $12,015 'B12
20 11 $9,705 Nov-08 $11,455 'B13
21 12 $9,210 Dec-08 $11,095 'B14
22 13 $8,665 Jan-09 $11,060 'B15
23 14 $7,985 Feb-09 $11,200 'B16

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200807/1

Just out of curiosity, did you see the formula I posted 15 minutes before
your response to Bernie? I am pretty sure it does what you want.

Rick


"lonnierudd via OfficeKB.com" <u11209@uwe wrote in message
news:87d4d763a0ea2@uwe...
Sorry, but I could have been more clear. Cell H4 is the difference in the
market value by month on the vehicle. On what I posted H4 is $528.07 for
the
month of July, 2008. On the table in cell B1 is entered the last day of
the
month we're looking at. This time it's July 31, 2008, so the formula needs
to
be pulled down from the cell beside June 2008, relatively speaking. In
June
the formula is E14-$H$4, so in July it's E15-$H$4. The month after the
date
we're looking at needs to equal what is in cell B10, in this case that
would
be what is beside August 2008. Also, the cells below that need to change
also,
so that September equals B11, October equals B12, etc. Next month, the
formula next to August will be E16-$H$4, and September will equal B10,
October will equal B11, etc. Does that make sense?

Bernie Deitrick wrote:
Lonnie,

You need to describe, in words, what it is that you want to do. Your
table doesn't make immediate
sense (what is in H4?), so it is impossible to decipher just what it is
that you want to do.

HTH,
Bernie
MS Excel MVP

I"ve asked this question before and didn't get an answer, so I thought
I'd
phrase it differently. Maybe it isn't possible? If I can't get it to
work in
[quoted text clipped - 47 lines]
22 13 $8,665 Jan-09 $11,060
'B15
23 14 $7,985 Feb-09 $11,200
'B16

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200807/1

Well, my pasting and explanation weren't very good in the original table, so
neither of these worked completely (my fault. I only pasted part of the table
and it didn't line up right). The table runs from B10 to B45 and changes each
month, and there isn't anything in Column C (Sorry again). I changed the
INDEX to reflect B10:B45 on Bob's answer, and took the $ signs off and
adjusted the length of the column a little and drug it down and it gives the
correct answer to July 2009 (Woo hoo!), although I must admit I am at a loss
as to understanding why and there's still a year and a half to go. Here's the
formula I used based on what Bob gave:

=INDEX($B$10:$B$45,13-MATCH($B$1,D5:$D$45,0))

Everything from August 2009 to December 2010 has #N/A on it now, probably
because the data ends on D45 I guess? I've never used the index and match
functions before. I need to look into those...

Rick's solution didn't work, but again, I think it was because the help I
tried to give with the columns and rows wasn't clear.

Bob Phillips wrote:
=INDEX($B$2:$B$13,13-MATCH($B$1,$D$16:$D$27,0))

I"ve asked this question before and didn't get an answer, so I thought I'd
phrase it differently. Maybe it isn't possible? If I can't get it to work
[quoted text clipped - 71 lines]
23 14 $7,985 Feb-09 $11,200
'B16

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200807/1

Well, it only looks like it works until July 2009. When I change the date the
other months are incorrect. What does the 13-Match do?

lonnierudd wrote:
Well, my pasting and explanation weren't very good in the original table, so
neither of these worked completely (my fault. I only pasted part of the table
and it didn't line up right). The table runs from B10 to B45 and changes each
month, and there isn't anything in Column C (Sorry again). I changed the
INDEX to reflect B10:B45 on Bob's answer, and took the $ signs off and
adjusted the length of the column a little and drug it down and it gives the
correct answer to July 2009 (Woo hoo!), although I must admit I am at a loss
as to understanding why and there's still a year and a half to go. Here's the
formula I used based on what Bob gave:

=INDEX($B$10:$B$45,13-MATCH($B$1,D5:$D$45,0))

Everything from August 2009 to December 2010 has #N/A on it now, probably
because the data ends on D45 I guess? I've never used the index and match
functions before. I need to look into those...

Rick's solution didn't work, but again, I think it was because the help I
tried to give with the columns and rows wasn't clear.

=INDEX($B$2:$B$13,13-MATCH($B$1,$D$16:$D$27,0))

[quoted text clipped - 3 lines]
23 14 $7,985 Feb-09 $11,200
'B16

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200807/1

Rick's solution didn't work, but again, I think it was because the
help I tried to give with the columns and rows wasn't clear.

Alright, assuming I got the column wrong (I still think the formula is
basically correct), put the following formula in D6 and copy it down (you
can copy it past your existing data if you want)...

=IF(C6="","",IF(C6<=B$1,D5-$H$4,INDEX(B$10:B$1000,1+DATEDIF(B$1,C6,"m"))))

Rick

Oh wow, it's close. I adjusted for the columns and here's what I got:

=IF(D6="","",IF(D6<=B$1,E5-$H$4,INDEX(B$10:B$45,1+DATEDIF(B$1,D6,"m"))))

This works up until the point where the formula is supposed to change to =B10,
then it hiccups. Here are two columns, the first of what it should be and the
second of what came out after the formula change. The table continues past
where it is needed, but I put it here so you can see what it's doing. The
first column is from B10 to B45, and the second is what it shows from E17 to
E45. From E5 to E16 is perfect.

$13,905 $12,920
$12,920 $12,920
$12,015 $11,455
$11,455 $11,455
$11,095 $11,060
$11,060 $11,200
$11,200 $11,200
$11,010 $10,580
$10,580 $10,580
$10,160 $9,705
$9,705 $9,705
$9,210 $8,665
$8,665 $7,985
$7,985 $7,985
$7,375 $6,990
$6,990 $6,990
$6,730 $6,690
$6,690 $6,745
$6,745 $6,745
$6,585 $6,280
$6,280 $6,280
$5,980 $5,660
$5,660 $5,660
$5,320 $4,945
$4,945 $4,485
$4,485 $4,485
$4,085 $3,825
$3,825 $3,825
$3,640 $3,600
$3,600
$3,605
$3,470
$3,260
$3,050
$2,830
$2,605


Rick Rothstein (MVP - VB) wrote:
Rick's solution didn't work, but again, I think it was because the
help I tried to give with the columns and rows wasn't clear.

Alright, assuming I got the column wrong (I still think the formula is
basically correct), put the following formula in D6 and copy it down (you
can copy it past your existing data if you want)...

=IF(C6="","",IF(C6<=B$1,D5-$H$4,INDEX(B$10:B$1000,1+DATEDIF(B$1,C6,"m"))))

Rick

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200807/1

I think we can resolve this rather quickly if I can only see what your
layout actually looks like. Can you send me a copy of your worksheet? Just
remove the NO.SPAM parts from my posted address
) and the rest is my real address.

Rick


"lonnierudd via OfficeKB.com" <u11209@uwe wrote in message
news:87d5f7c67c1b8@uwe...
Oh wow, it's close. I adjusted for the columns and here's what I got:

=IF(D6="","",IF(D6<=B$1,E5-$H$4,INDEX(B$10:B$45,1+DATEDIF(B$1,D6,"m"))))

This works up until the point where the formula is supposed to change to
=B10,
then it hiccups. Here are two columns, the first of what it should be and
the
second of what came out after the formula change. The table continues past
where it is needed, but I put it here so you can see what it's doing. The
first column is from B10 to B45, and the second is what it shows from E17
to
E45. From E5 to E16 is perfect.

$13,905 $12,920
$12,920 $12,920
$12,015 $11,455
$11,455 $11,455
$11,095 $11,060
$11,060 $11,200
$11,200 $11,200
$11,010 $10,580
$10,580 $10,580
$10,160 $9,705
$9,705 $9,705
$9,210 $8,665
$8,665 $7,985
$7,985 $7,985
$7,375 $6,990
$6,990 $6,990
$6,730 $6,690
$6,690 $6,745
$6,745 $6,745
$6,585 $6,280
$6,280 $6,280
$5,980 $5,660
$5,660 $5,660
$5,320 $4,945
$4,945 $4,485
$4,485 $4,485
$4,085 $3,825
$3,825 $3,825
$3,640 $3,600
$3,600
$3,605
$3,470
$3,260
$3,050
$2,830
$2,605


Rick Rothstein (MVP - VB) wrote:
Rick's solution didn't work, but again, I think it was because the
help I tried to give with the columns and rows wasn't clear.

Alright, assuming I got the column wrong (I still think the formula is
basically correct), put the following formula in D6 and copy it down (you
can copy it past your existing data if you want)...

=IF(C6="","",IF(C6<=B$1,D5-$H$4,INDEX(B$10:B$1000,1+DATEDIF(B$1,C6,"m"))))

Rick

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200807/1

Thank you. It's on its way.

Rick Rothstein (MVP - VB) wrote:
I think we can resolve this rather quickly if I can only see what your
layout actually looks like. Can you send me a copy of your worksheet? Just
remove the NO.SPAM parts from my posted address
) and the rest is my real address.

Rick

Oh wow, it's close. I adjusted for the columns and here's what I got:

[quoted text clipped - 57 lines]

Rick

--
Message posted via http://www.officekb.com

For those following this thread, the OP sent me a copy of the workbook
offline. After looking at it, I suggested he try this formula...

=IF(D6<=B$1,E5-$H$4,INDEX(B$10:B$1000,DATEDIF(B$1-DAY(B$1)+1,D6-DAY(D6)+1,"m")))

in E6 and copy it down. He wrote back to me (offline) that the formula
worked, so I guess we can consider this question as having been answered.

Rick


"lonnierudd via OfficeKB.com" <u11209@uwe wrote in message
news:87d667975a033@uwe...
Thank you. It's on its way.

Rick Rothstein (MVP - VB) wrote:
I think we can resolve this rather quickly if I can only see what your
layout actually looks like. Can you send me a copy of your worksheet? Just
remove the NO.SPAM parts from my posted address
) and the rest is my real address.

Rick

Oh wow, it's close. I adjusted for the columns and here's what I got:

[quoted text clipped - 57 lines]

Rick

--
Message posted via http://www.officekb.com

And Rick is my hero...

Rick Rothstein (MVP - VB) wrote:
For those following this thread, the OP sent me a copy of the workbook
offline. After looking at it, I suggested he try this formula...

=IF(D6<=B$1,E5-$H$4,INDEX(B$10:B$1000,DATEDIF(B$1-DAY(B$1)+1,D6-DAY(D6)+1,"m")))

in E6 and copy it down. He wrote back to me (offline) that the formula
worked, so I guess we can consider this question as having been answered.

Rick

Thank you. It's on its way.

[quoted text clipped - 10 lines]

Rick

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200807/1