Changing cell references

T

Tracey

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.
 
P

Peo Sjoblom

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
 
T

Tracey

Thanks.

Peo Sjoblom said:
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
Click to expand...
 
C

cwilson

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 Edit>Replace. Replace 'Sheet 2004' with
'Sheet 2005'. In my test I selected "Replace all" and it only replce those
in highlighted column.

HTH
cwilson
 
Top