Static formula when inserting new rows/columns?
 

I have a sheet that i want to make a macro for. However i dont want the
formulas to update to the new cell references when i insert new
columns/rows. Ive tried locking the cells but the it still updates.

The other idea ive had is just add the formula editing to the macro.
However this is a bit long winded.

Thanks in advance


--
nfe
------------------------------------------------------------------------
nfe's Profile: http://www.excelforum.com/member.php...o&userid=34037
View this thread: http://www.excelforum.com/showthread...hreadid=538007

Static formula when inserting new rows/columns?
 

Use the INDIRECT function:

=INDIRECT("A1")

will always return the current A1 cell's value, no matter what happens with row insertion/deletions.

HTH,
Bernie
MS Excel MVP


"nfe" wrote in message
...

I have a sheet that i want to make a macro for. However i dont want the
formulas to update to the new cell references when i insert new
columns/rows. Ive tried locking the cells but the it still updates.

The other idea ive had is just add the formula editing to the macro.
However this is a bit long winded.

Thanks in advance


--
nfe
------------------------------------------------------------------------
nfe's Profile: http://www.excelforum.com/member.php...o&userid=34037
View this thread: http://www.excelforum.com/showthread...hreadid=538007

Static formula when inserting new rows/columns?
 

Many thanks, i was unaware of this function.


--
nfe
------------------------------------------------------------------------
nfe's Profile: http://www.excelforum.com/member.php...o&userid=34037
View this thread: http://www.excelforum.com/showthread...hreadid=538007

Static formula when inserting new rows/columns?
 

Apologies it doesnt seem to do all i require of it, or i dont have the
grasp of it ..

I want to write something like, =Indirect("(i4/j4-1)*100)") but it
doesnt seem to like this syntax?

Thanks


--
nfe
------------------------------------------------------------------------
nfe's Profile: http://www.excelforum.com/member.php...o&userid=34037
View this thread: http://www.excelforum.com/showthread...hreadid=538007

Static formula when inserting new rows/columns?
 

You can only have a cell reference in the INDIRECT( ) function. I think
what you are trying to do is this:

=(INDIRECT("I4")/INDIRECT("J4") -1)*100

Hope this helps.

Pete