Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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")) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Stop Date Values from Changing to Formulas | Excel Worksheet Functions | |||
How do I stop last number from changing to a 0 in excel | Excel Discussion (Misc queries) | |||
When I add a row to excel how can I stop formulae changing | Excel Discussion (Misc queries) | |||
how do i get excel to stop changing my input 5-9 to 9-May???? | Excel Discussion (Misc queries) | |||
How do I stop excel from changing single numbers like 1 into a de. | Excel Discussion (Misc queries) |