Changing cell references
 

I am building a spreadsheet to pull data by year from other spreadsheets. On
this spreadsheet, my cell formulas would reference spreadsheet "Sheet 2004"
for data from 2004, "Sheet 2005" for data from 2005, etc. Is there a simple
way to change the cell references to the correct sheet name if the row and
column references remain the same? For example:

'Sheet 2004'!$B$5 needs to be changed to 'Sheet 2005'!$B$5 in subsequent
columns.

Thanks.

try Edit Links under the edit menu.

"Tracey" wrote:

I am building a spreadsheet to pull data by year from other spreadsheets. On
this spreadsheet, my cell formulas would reference spreadsheet "Sheet 2004"
for data from 2004, "Sheet 2005" for data from 2005, etc. Is there a simple
way to change the cell references to the correct sheet name if the row and
column references remain the same? For example:

'Sheet 2004'!$B$5 needs to be changed to 'Sheet 2005'!$B$5 in subsequent
columns.

Thanks.

Yes and no, there is no built in way that will aoutmatically give you the
next sheet if you copy a formula, if indeed you are using 2004 and 2005 etc
there is a workaround

=INDIRECT("'Sheet "&2004+COLUMN(A:A)-1&"'!B5")

copied across will increase 2004, 2005, 2006 etc

if you want to copy down use

=INDIRECT("'Sheet "&2004+ROW(1:1)-1&"'!B5")

Regards,

Peo Sjoblom


"Tracey" wrote:

I am building a spreadsheet to pull data by year from other spreadsheets. On
this spreadsheet, my cell formulas would reference spreadsheet "Sheet 2004"
for data from 2004, "Sheet 2005" for data from 2005, etc. Is there a simple
way to change the cell references to the correct sheet name if the row and
column references remain the same? For example:

'Sheet 2004'!$B$5 needs to be changed to 'Sheet 2005'!$B$5 in subsequent
columns.

Thanks.

Thanks.

"Peo Sjoblom" wrote:

Yes and no, there is no built in way that will aoutmatically give you the
next sheet if you copy a formula, if indeed you are using 2004 and 2005 etc
there is a workaround

=INDIRECT("'Sheet "&2004+COLUMN(A:A)-1&"'!B5")

copied across will increase 2004, 2005, 2006 etc

if you want to copy down use

=INDIRECT("'Sheet "&2004+ROW(1:1)-1&"'!B5")

Regards,

Peo Sjoblom


"Tracey" wrote:

I am building a spreadsheet to pull data by year from other spreadsheets. On
this spreadsheet, my cell formulas would reference spreadsheet "Sheet 2004"
for data from 2004, "Sheet 2005" for data from 2005, etc. Is there a simple
way to change the cell references to the correct sheet name if the row and
column references remain the same? For example:

'Sheet 2004'!$B$5 needs to be changed to 'Sheet 2005'!$B$5 in subsequent
columns.

Thanks.

Oops! Read the post wrong. Apologies for any harm.

I tried this on a small sample and it seemed to work. Highlight the column
that you want to change and go to EditReplace. Replace 'Sheet 2004' with
'Sheet 2005'. In my test I selected "Replace all" and it only replce those
in highlighted column.

HTH
cwilson