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