Excel 2007 : Cell designation in formula changes after sort. Is there a Better Way?

[twotwohard4]

Believe I have an error in my original set-up on this sheet. My goal is to be able to enter data in row 3 and the worksheet populate and total up the values. Which currently my sheet does, it populates and totals correctly, however, it does not play well with sorting.
Rows 1,2,3 should always stay the same.
Then when I try to sort rows 4 thru 48 by column BU from largest to smallest...
the formulas do not work since my reference cells used in the formula are being relocated after the sort.
It makes sense that I4 is no longer I4, etc. after the sort.
Is there a basic formula or a set-up I should be using?
Is there a way to reference a row location by the name in column A rather than row#4

Thanks for any guidance?


HRDerby2012-PickSheet.xlsx

[dilipandey]

Hi Twotwohard4,

Freeze the formulas i.e., put $ sign and it then sort.

I have applied $ sign on the formulas for row #4 i.e. for Woj and after sort, it went to row #9 but the formula was still picking up the right reference.

To apply $ sign, select the cell and press F4. You might want to freeze only row 3 so put $ sign before 3 in formulas. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[SpreadSheetNinja]

aka for referanceing the B3 for example, you wana type it like =B$3 so the 3 sticks, the problem you have is when they trade height from row 3 so it becomes higher the longer down the formula is used :P by writeing it like =B$3 then the 3 wont change no matter what, but if you use the formula sideways column then b will become C D E so on, you can lock that down too by writeing =$B$3
Hope that clears it more up
it will take a while to change the whole sheet, but you can probebly just use ctrl - H and replace any text whit =B3 into =B$3
and replace whole sheet, or just the area you have marked, and after that do whit =C3 to =C$3 and so on and so on, takes a while, unless you can do it whit VBA
Also a tip for later if you want completely permanent cell referances :P you can use the COPY, PASTE AS LINK feature by right clicking or selecting the arrow down on the paste tab up on the ribbon, it will for example if you ctrl - c on cell B3, then click the cell right under and paste as link, then it will be linked as =$B$3
Noteably you can paste to multiple cells at same time, or just copy your newly pasted link to cell to all the other cells.. meh theres many ways of doing this.