Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When inserting a simple "Sum" formula all I get is the formula. | Charts and Charting in Excel | |||
Inserting a Space in The Same Place Formula | Excel Worksheet Functions | |||
formula a1=b1+c1 inserting column after a1 changes formula a1=c1. | Excel Discussion (Misc queries) | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
Inserting row but with formula carried down | Excel Worksheet Functions |