stop excel changing formulas

  • Thread starter Thread starter sollidamra
  • Start date Start date
S

sollidamra

i have a summary worksheet which looks up stock from other worksheets and
totals them for me. The problem is if i insert or delete a row into my stock
excel automatically adjusts the formula range up or down. How do i keep the
formula exactly as typed without the range being automatically adjusted? Matt
 
hi
not sure but i think you are taking about absolute reference verses relative
reference
a relative reference looks like this...A1....and excel will under certain
conditions make adjustment to this.
a absolute reverence looks like this ...$A$1.... and excel will not adjust
it period.
the added dollar signs makes it absolute to excel.
you can have absolute rows and relative columns and vice versa so that you
can freeze one or the other if you have some copy/paste to do. can get tricky.
read up on absolute references in help.

Regards
FSt1
 
Hi, I think u missed the point.
formula in a1, sheet 1: =sum(sheet2!a1:a3)
when u add row to a2, sheet 2, formula will change to =sum(sheet2!a1:a4),
no matter if absolute or relative reference, a1 or r1c1.
 
You're right, he did miss the point, because you didn't bother to post your
formula in the first place. What was anyone supposed to do other than guess
at what you had?

If you don't want the range to change under any circumstances, use the
Indirect function, as in:
=SUM(INDIRECT("Sheet2!A1:A3"))

Regards,
Fred.
 
worksheets and totals them for me. The problem is if i insert or
delete a row into my stock excel automatically adjusts the formula
range up or down. How do i keep the formula exactly as typed
without the range being automatically adjusted? Matt
Click to expand...
formula in a1, sheet 1: =sum(sheet2!a1:a3)
when u add row to a2, sheet 2, formula will change to
=sum(sheet2!a1:a4),
no matter if absolute or relative reference, a1 or r1c1.
Click to expand...

One way:
=SUM(INDIRECT("sheet2!a1:a3"))
 
hi
you are right. ignore my post.

regards
FSt1

Alojz said:
Hi, I think u missed the point.
formula in a1, sheet 1: =sum(sheet2!a1:a3)
when u add row to a2, sheet 2, formula will change to =sum(sheet2!a1:a4),
no matter if absolute or relative reference, a1 or r1c1.
Click to expand...
 
Fred, just to make it clear, I am not Matt under the nick sollidamra, he
posted the problem. I just want to point out FSt1 probably did not get it
once I went through the question and answer. Did not want to offend
anybody... If I was Matt I would try to describe the problem in more details.
Anyway, indirect works, thanks.
 
thanks for replies people.
sorry putting formula in would have helped, wasn't thinking..
my formula:-
{=SUM((Engineers!$B$2:$B$500="Cisco AP")*(Engineers!$E$2:$E$500))}
this looks in engineers worksheet, finds all occureneces of Cisco AP's in
column B and adds up the quantities which are in column E. If I use a piece
of stock and delete the row excel changes the ranges to $B$2:$B$499 -
$E$2:$E$499, what i want is for it to stay permanently at $B2:B$500 -
$E$2:$E$500...

I think tho Fred may have hit the nail on the head with INDIRECT function,
if this is true should it read
{=SUM(INDIRECT((Engineers!$B$2:$B$500="Cisco AP")*(Engineers!$E$2:$E$500)))}
I'll give it a go, thanks again.
 
Back
Top