Absolute cell references across worksheets

Hi
do you always want to reference column C/D if yes maybe INDIRECT or
OFFSET could help you. e.g. to get the values from B1 and C1 on your
second sheet you may use
1. Using INDIRECT
=INDIRECT("'sheet2'!B1")
and
=INDIRECT("'sheet2'!C1")

2. Using OFFSET
=OFFSET('sheet2'!$A$1,0,1)
and
=OFFSET('sheet2'!$A$1,0,2)

--
Regards
Frank Kabel
Frankfurt, Germany
"RuthC" schrieb im Newsbeitrag
...
In my Excel 2000 spreadsheet I have 2 worksheets. I use sheet 2 to
gather monthly statistics where column C always holds the current
month's data and column D holds last month's data. Sheet 1 is a report
of this month's and last month's data (Sheet 2, column C and D) and an
average of the most recent 12 months.

When I insert a new column in sheet 2 to add a new month's data, the
cell references in sheet 1 "shift", so that I end up with column D and
E. It doesn't seem to matter if I use relative or absolute cell
references.

Can I somehow modify my cell reference to make this work without
redesigning my spreadsheet each month?

This isn't the most elegant idea, and it assumes you have
only data, not formulas, in Sheet 2 Columns C & D. On
Sheet 2, right click the Column C header to select the
whole column and open a shortcut menu. Choose Copy from
the shortcut menu. Then right click the Column D header
to select Column D, and choose Paste from the shortcut
menu. This will fill Column D with the values from Column
C. Then, right click the Column C header again, and
choose Clear Contents, to empty Column C, so you can
enter your new data.

If you need to preserve the contents of Column D, before
overwriting them with Column C's values, you can use the
same right click method to copy Column D and paste its
information elsewhere.

I don't yet see a good way to modify your formulas to
handle the situation, but if I think of something, I'll
post another message. I believe Excel is going to modify
your references anytime you delete or insert a column in
the spreadsheet.
-----Original Message-----
In my Excel 2000 spreadsheet I have 2 worksheets. I use
sheet 2 to gather monthly statistics where column C
always holds the current month's data and column D holds
last month's data. Sheet 1 is a report of this month's
and last month's data (Sheet 2, column C and D) and an
average of the most recent 12 months.

When I insert a new column in sheet 2 to add a new
month's data, the cell references in sheet 1 "shift", so
that I end up with column D and E. It doesn't seem to
matter if I use relative or absolute cell references.

Can I somehow modify my cell reference to make this work
without redesigning my spreadsheet each month?
.

The OFFSET function works perfectly for my needs. Thank you all for your prompt help!

Insert a column before column A in sheet 1, and hide it?