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

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!

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

Nel post
*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

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

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


--
Jon Quixley
------------------------------------------------------------------------
Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
View this thread: http://www.excelforum.com/showthread...hreadid=554379