Complex Time Window / date based calculation

[stef]

Excel 2002 SP3
Win XP HE

*Follow-up to: microsoft.public.excel*

Hi,

I have the following:
Columns
A B C D E F G H I J
Date Field 1 Field 2 Field 3 Field 4 Field 5 Field 6 Field 7 Field 8
Field 9
12/31/1996 xyz 58979 23539 1880 1937 1837 1930 100
1/30/1997 xyz 58979 23539 1925 1965 1787 1822 -5.60% 94.40
2/28/1997 xyz 58979 23539 1925 1965 1787 1822 -5.60% 94.40
3/31/1997 abc 58979 23539 1925 1965 1787 1822 -5.60% 94.40
etc.

I need to be able to modify the last column (column J) so that the
number 100 starts at TODAY() - 1 year (the "-" is a minus sign) as well
as TODAY()-3 years, TODAY()-5 years, TODAY()-10 years, from inception
(which is the one from date 12/31/1996).

What I want to do is give the user the ability to choose whatever time
window he wants and then Column J will adjust its calculations by
reindexing at 100 from whatever starting date is chosen. Best would be
being able to choose a time window such as 1 3, 5 years, etc. ; OR a
specific date--whichever one chooses to do.

Don't know if possible to be done in one configuration or 2 (one based
on # of years and another based on any of the specific dates included in
Column A).

I am open to various means, drop boxes, macros, or even perhaps Pivot
Table (but not very experienced with them)....

Any Excel experts out there that can give me a hand?

Thanks a million.

 

[stef]

In original post, the number "100" should be in Column J below "Field 9"
and above the first "94.40".
(Sorry about the formatting but have not figured out how to insert a
proper table into a plain text message yet.)