Absolute cell references across worksheets

[Guest]

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?

 

[Vasant Nanavati]

It's hard to be specific without seeing your actual formula, but using the
OFFSET function would probably work. So on Sheet1 , if an original formula
references Sheet2!B3, use instead:

OFFSET(Sheet2!$A3,0,1)

Instead of a reference to Sheet2!C3, use:

OFFSET(Sheet2!$A3,0,2)

This assumes that column A on Sheet2 is never moved.

--

Vasant

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?

 

[Frank Kabel]

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

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?

 

[Guest]

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?

 

[Guest]

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