Excel - Cell formula change on insert

[Malcav]

Hey
I am doing some complex marco work and come up against a problem.
I have 53 columns of different formula pointing at a row on another sheet. later i have to insert a line on that sheet. I want the formula not to change. How is this done?

THanks
Malc

 

[SkipVought]

Hi,

Don't use INSERT. Rather append in the row after the last row and then SORT into place.

Skip,
[sub]
[red][/red]
[/sub]

 

[Malcav]

I cant append cos the blank row at the bottom holds the formatting for the user to add new info to the database.

 

[xlbo]

Then you have an issue with your design

hold formatting and lookups on seperate sheets - NOT at the bottom of your dataset....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KenWright]

Depends on your data but you could always make a copy of the sheet that the formulas point to, edit that sheet and then paste back over thge original. That way your formulas all point as they were.

The other option, albeit make sure it doesn't break anything, is to convert all your formulae to text, perhaps by replacing = with %% or some other character(s), do your changes and then revert them to real formulas again.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------