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

R

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!
 
F

Franz Verga

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!
Click to expand...

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
 
J

Jon Quixley

Rachel,

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

Cheers
Jo
 
Top