stop excel changing formulas

[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

 

[FSt1]

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

 

[Alojz]

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.

 

[Fred Smith]

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.

 

[MyVeryOwnSelf]

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

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.

One way:
=SUM(INDIRECT("sheet2!a1:a3"))

 

[FSt1]

hi
you are right. ignore my post.

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.

 

[Alojz]

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.

 

[sollidamra]

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.