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?

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

Autumn Dreams wrote:
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?

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" wrote:

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

Autumn Dreams wrote:
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?

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

Autumn Dreams wrote:
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" wrote:

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

Autumn Dreams wrote:
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?