Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
nfe
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Bernie Deitrick
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
nfe
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
nfe
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Pete_UK
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
When inserting a simple "Sum" formula all I get is the formula. Chris Bowling Charts and Charting in Excel 2 February 28th 06 06:22 PM
Inserting a Space in The Same Place Formula carl Excel Worksheet Functions 2 March 5th 05 12:10 AM
formula a1=b1+c1 inserting column after a1 changes formula a1=c1. darshan Excel Discussion (Misc queries) 3 February 11th 05 01:31 PM
inserting data from a row to a cell, when the row number is specified by a formula in a cell [email protected] New Users to Excel 2 January 6th 05 08:18 AM
Inserting row but with formula carried down Steve R Excel Worksheet Functions 1 December 15th 04 04:23 PM


All times are GMT +1. The time now is 12:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"