stop excel changing formulas
 

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

stop excel changing formulas
 

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

"sollidamra" wrote:

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

stop excel changing formulas
 

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.

"FSt1" wrote:

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

"sollidamra" wrote:

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

stop excel changing formulas
 

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.

"Alojz" wrote in message
...
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.

"FSt1" wrote:

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

"sollidamra" wrote:

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

stop excel changing formulas
 

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"))

stop excel changing formulas
 

hi
you are right. ignore my post.

regards
FSt1

"Alojz" wrote:

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.

"FSt1" wrote:

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

"sollidamra" wrote:

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

stop excel changing formulas
 

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.

"Fred Smith" wrote:

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.

"Alojz" wrote in message
...
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.

"FSt1" wrote:

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

"sollidamra" wrote:

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

stop excel changing formulas
 

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.

"sollidamra" wrote:

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