Change Year Date In A Formula Throughout The Spreadsheet

[Guest]

I am using the formula =COUNTIF('Jan-06'!$D$3:$D$500,"Sales"). We copy the
data down within the same spreadsheet to create one for the new year. The
Jan-06 is based on the tab name, therefore when the year changes to 2007 the
added new tab will be Jan-07. This is used for all 12 months in 12 columns
with the "Sales" changing for each item that needs counting. Is there a way
to create a formula that when the year is changed in the first formula in
Column B5, the year in every formula in the spreadsheet is changed within
that section only?

 

[Pete_UK]

As your tab names will be text, you can do Find and Replace to do this
in one operation.

Highlight all the cells in the new sheet (CTRL-A, or click the
intersection between row and column identifiers), then Edit | Replace
(or CTRL-H) and enter in the two boxes:

Find What: -06
Replace With: -07

then click Replace All. This should accomplish what you want to do.

Hope this helps.

Pete

 

[Guest]

This works, except a window comes up "Update Values -06" and opens into My
Documents. To get rid of it you have to click cancel for each formula
highlighted and that would entail 240 times. How do I get past this?

 

[Pete_UK]

I've never come across this, so sorry, can't advise what's happening.
Perhaps it means you've got links to other workbooks.

Pete