PLZ help with excel, simple question. formula keeps changing

[lukasz70]

hi, i need some help with excel,
i gotta a project due, and everything works in excel except when i update some info and shift the rows up, the formulas on the other worksheets change automatically, i dont want the formulas to change, i want them to stay the same and keep using the data cell im making it use, but instead when i move the rows up the formula follows that data and changes itself.

here is a sample of what i mean, hopefully it will open

http://lukasz70.50megs.com/Book1.xls right click save as.

k, here is my problem, the formula at the bottom calculates from rows 3-9, and row 10 is new data thats you just put in, but you still only want to use row 3-9, so you delete the data from row 3, and shift the data up, so that what you put into row 10 is now in row 9. so you kinda taking out teh old data and putting in the new. the problem is when you shift the rows, the formula changes and it only counts the thing from row 3-8, i want it to keep counting to row 9 but it changes itself

is there any way to lock the formula? make it stay the same so it doesn't follow the data/change by itself?

thank you

 

[RalphA]

Re: PLZ help with excel, simple question. formula keeps chan

To fix a cell or a range of cells, use absolute addressing, accomplished by prefixing the $ sign, thus:

To refer to cell B4, no matter how many rows you add or delete, use B$4.
To refer to cell B4, no matter how many columns you add or delete, use $B4.
To refer to cell B4, no matter how many rows and/or columns you add or delete, use $B$4.

Hopefully, you can now make the necessary changes to your formula(s) to accomplish your goals.

 

[Aladin Akyurek]

Re: PLZ help with excel, simple question. formula keeps chan

What is the formula that you have?

 

[lukasz70]

Re: PLZ help with excel, simple question. formula keeps chan

it still doesn't work
k the formula is just a sum formula =SUM(B3:O9)
it goes to row 9 row 10 is new data,
so here is what i do, i delete row 3, and and want row 10 to be row 9, so i shift everythig to up one row, but the formula changed and becomes =SUM(B3:O8) it changes by one row,

i did the $ so the formula was =SUM(B$3:O$9) but it stilll changes to =SUM(B$3:O$8)

 

[Aladin Akyurek]

Re: PLZ help with excel, simple question. formula keeps chan

it still doesn't work
k the formula is just a sum formula =SUM(B3:O9)
it goes to row 9 row 10 is new data,
so here is what i do, i delete row 3, and and want row 10 to be row 9, so i shift everythig to up one row, but the formula changed and becomes =SUM(B3:O8) it changes by one row,

i did the $ so the formula was =SUM(B$3:O$9) but it stilll changes to =SUM(B$3:O$8)

=SUM(INDIRECT("B3:O9"))

 

[lukasz70]

Re: PLZ help with excel, simple question. formula keeps chan

I LOVE YOU

it works!!!
i just have to put this into about 64 formulas, but it works,
here is the actual formula that i edited =SUM(INDIRECT("DATA!D6:O8"))

i'll know once im putting your formula in, but i might have to do that to parts of this formula to thats gonna mess me up lol,
=IF(SUM(DATA!D51:O53)=0,SUM(B15:D15)/(1-AVERAGE(E7,E10,E13))-(SUM(B15:D15)),SUM(DATA!D51:O53))

 

[RalphA]

Re: PLZ help with excel, simple question. formula keeps chan

Sorry, my suggestion is wrong anddoesn't work for the row deletions. However, Aladin's method works like a charm! Kudos to you, oh Aladin! ray: