Is there a way to sort without losing individual set formulas?

[Rachael-R]

I basically have 5 worksheets that I enter data - formulas are set for each
row and are different for each. The value I get for the formula is then
transferred to a summary sheet. But if I sort any one of the 5 data sheets
my formulas get all screwed up. Plus if I have to add or delete any rows the
formulas again get screwed up. Is there any way to lock in the formulas. I
tried protecting but that doesn't seem to do it. Help!

[Franz Verga]

Nel post news:[email protected]
*Rachael-R* ha scritto:

> I basically have 5 worksheets that I enter data - formulas are set
> for each row and are different for each. The value I get for the
> formula is then transferred to a summary sheet. But if I sort any
> one of the 5 data sheets my formulas get all screwed up. Plus if I
> have to add or delete any rows the formulas again get screwed up. Is
> there any way to lock in the formulas. I tried protecting but that
> doesn't seem to do it. Help!

Hi Rachel,

What do you mean with "screwed up"?
Maybe you have some empy column beetwen the data you sort and the zone with
formulas: for a good sorting you need no empty row/column in your list.


--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

[Jon Quixley]

Rachel,

Have you tried modifying all your formulae to absolute references rather than relative ones?
A "normal" formula most people use consists of relative references a simple example is in cell D5 "=A2-B3". The way Excel looks at this is to start from cell D5 where the formula is and work out where A2 and B3 are relative to D5, these are 3 up and 3 left (A2) and 2 up and 2 left (B3). By copying or sorting this, the relative location is preserved, but you will end up with the wrong answer because the formula is still going 3 up, 3 left and 2 up and 2 left. Write the same formula with the dollar sign infront of both letters and numbers in cell D5 =$A$2-$b$3 will fix the formula to ALWAYS go back to those cells regardless of where the formula is on the sheet - this is an absolute reference.

Cheers
Jon