some complicated programming me thinks, update worksheet function

[mike]

afternoon all,
and merry christmas and happy new year to you!

ok, so here's the situation:
i have a workbook its records dates and amounts in two columns plus other
details in the remaining columns.
i have named one sheet "current year" where all items are entered for the
current year.
i wrote a macro which copies all the data to a new sheet called
(year-1)Archived, where year minus one is a function which looks at the year
today and names the sheet the previous year. the macro then clears the
"current year" sheet enabling it to be used again.

i now need to create an MI sheet, however, rather than creating lots of
sheets for each period, i would like the user to be able to enter a year in
two cells of this sheet, eg. 2004 and 2005, then the sheet would update all
worksheet functions to search the archived sheets for those specific years.
it needs to search worksheet functions because i have used simple if
statements to form MI and graphs.
therefore i am looking for some suggestions as to how my if statements could
be made into a macro eliminating the cell values currently listed, and
whether it is possible to update the worksheet function to a specific
worksheet location from the value in another cell.

here is some examples of what im currently using:

=IF('2004 Archived'!A5>$C$44,'2004 Archived'!I5,0)
this if function looks to see if the value in A5 is greater than a year ago
today, and if it then show the amount given in I5, else show 0.

=IF('2004 Archived'!J5=1,1,0)
this function looks in J5 to see if the value is 1, which is a
classification value, and if it is then the cell shows 1.
i have then added the range where this formula appears to show how many
occurrences of classification 1 there were in the given year.

i look forward to your responses
and thank you in advance for your time.

mike

 

[K Dales]

Mike: This is possible and not too bad, but I don't have time available
right now to actually work out and test the code. Basic idea:
1) You will need to loop through each sheet in your workbook
2) Use a For Each loop with the
Worksheet.Cells.SpecialCells(xlCellTypeFormulas) to find all the cells on the
sheet that contain formulas
3) You can see if the formula contains the word 'Archived' (InStr function)
to see if it refers to one of the archive sheets - if so you can use the
position returned by InStr and subtract 5 to find the year; then replace with
the year that is specified in the cell where it is entered.

I think this process should work. However, the better solution (but more
extensive to 'fix' your current workbook) would be to replace all your
references to the archive sheet with the INDIRECT function. Since the
INDIRECT function allows you to use a string to specify the address, you can
build a string that uses the cell value to refer to a particular year's
"archive"; e.g. your 2nd example could be written as:
=IF(INDIRECT("'"&A1&" Archived'!J5")=1,1,0)
This will calculate based on a year entered in cell A1; no VBA code needed.

Sorry I can't give complete code right now, but hope these pointers help get
you going at least.