Well this is a tricky one I have a attached a before and after shot of what i'm trying to achieve.
The formula is for Column I- it needs to take the date purchased and find what CPI index was valid for that period which is called the Purcahse Date CPI. Then the
(Original purchase cost * the purcahse Date CPI) divided by The last CPI figure 123.(this last figure never changes.
If the date is pre 1/7/85 it needs to write PRE CGT
If the date is after 1/10/99 then it just puts the Original Purchase cost.
Also the Sheet 1 needs to be hidden so it doesnt show up/ or can't be accessed by anyone but myself.
Hope thats clear.