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.