I have a workbook named Operations Vital signs. This workbook has tabs as
follows: Blank / 2008 / 2009 / 2010 / etc.

Each tab has user entered information which is stored on a shared network
drive. The Operations Vital Signs workbook is accessed by Company Vital Signs
workbook which now also has tabs: 2008 / 2009 / 2010 / etc.

The original workbook had no tabs & cell referenced
Operationsvitalsigns.xls. Once tabs were added the reference stopped working.

Is there a way I can tell each tabs references to look at the corresponding
years tabs in the other workbook other than hand correcting them? As it
stands I have to do the following about a million times:

click cell
highlight : #REF from ='[Operations Vital Signs 2008.xls]#REF'!$Q$25
change : #REF to ='[Operations Vital Signs 2008.xls]2009'!$Q$2
click next cell

Isn't there same way to mass modify the bad reference for the whole sheet?
We are going to add a new tab each year. Please show me what I am missing.
Thank you

Find & Replace should do the trick.

Select the range containing the REF errors and fire up the Find & Replace
dialog (Ctrl+H), then find #REF and replace with the correct tab name.

Sounds too simple to be true, but I hope it solves your problem.

Joerg



"Ryan" wrote in message
...
I have a workbook named Operations Vital signs. This workbook has tabs as
follows: Blank / 2008 / 2009 / 2010 / etc.

Each tab has user entered information which is stored on a shared network
drive. The Operations Vital Signs workbook is accessed by Company Vital
Signs
workbook which now also has tabs: 2008 / 2009 / 2010 / etc.

The original workbook had no tabs & cell referenced
Operationsvitalsigns.xls. Once tabs were added the reference stopped
working.

Is there a way I can tell each tabs references to look at the
corresponding
years tabs in the other workbook other than hand correcting them? As it
stands I have to do the following about a million times:

click cell
highlight : #REF from ='[Operations Vital Signs 2008.xls]#REF'!$Q$25
change : #REF to ='[Operations Vital Signs 2008.xls]2009'!$Q$2
click next cell

Isn't there same way to mass modify the bad reference for the whole sheet?
We are going to add a new tab each year. Please show me what I am missing.
Thank you

Thank you, very easy yet exactly what I needed.

One remaining problem. When I attempt to fix the cell references in tabs
2011 - 2020, it sends back an error message that there is no data in these
sheets.

"Joerg Mochikun" wrote:

Find & Replace should do the trick.

Select the range containing the REF errors and fire up the Find & Replace
dialog (Ctrl+H), then find #REF and replace with the correct tab name.

Sounds too simple to be true, but I hope it solves your problem.

Joerg



"Ryan" wrote in message
...
I have a workbook named Operations Vital signs. This workbook has tabs as
follows: Blank / 2008 / 2009 / 2010 / etc.

Each tab has user entered information which is stored on a shared network
drive. The Operations Vital Signs workbook is accessed by Company Vital
Signs
workbook which now also has tabs: 2008 / 2009 / 2010 / etc.

The original workbook had no tabs & cell referenced
Operationsvitalsigns.xls. Once tabs were added the reference stopped
working.

Is there a way I can tell each tabs references to look at the
corresponding
years tabs in the other workbook other than hand correcting them? As it
stands I have to do the following about a million times:

click cell
highlight : #REF from ='[Operations Vital Signs 2008.xls]#REF'!$Q$25
change : #REF to ='[Operations Vital Signs 2008.xls]2009'!$Q$2
click next cell

Isn't there same way to mass modify the bad reference for the whole sheet?
We are going to add a new tab each year. Please show me what I am missing.
Thank you