Macro changes formula in Excel
Posted by Leanne on March 14, 2001 12:51 PM
I am working with a multi-worksheet Excel spreadsheet
and using a macro to move data on one sheet down when
a button is pressed. When the data moves down, it
changes the formulas on another page and moves with
the old information. I want the cells in the formula
to be constant, but nothing I have tried seems to work.
I used the $, I tried using OFFSET as well as ADDRESS.
Do you have any suggestions?
Thanks!
Posted by Mark W. on March 14, 2001 2:36 PM
Use INDIRECT()
Posted by Mark W. on March 14, 2001 3:04 PM
BTW...Here's the tip from Help
When you create a formula that refers to a cell,
the reference to the cell will be updated if the
cell is moved by using the Cut command to delete
the cell or if the cell is moved because rows or
columns are inserted or deleted. If you always
want the formula to refer to the same cell
regardless of whether the row above the cell is
deleted or the cell is moved, use the INDIRECT
worksheet function. For example, if you always
want to refer to cell A10, use the following
syntax:
INDIRECT("A10")
