How to always make C5=B5 when I sort my data.

I hope I explain this correctly....I have a table created in my sheet along with a pareto chart. I always want it to be sorted by column B (largest to smallest). Time to time a user will be adding rows or modifying existing rows with different values (only columns A and B).

C5 is (C5=B5) and the rest of the cells under are basic math formulas.

Problem is for example, say I changed the value in B7 to 30 which is largest number. If I now sort my data by column B largest to smallest, it screws up my C5. I always want C5 to equal B5. I tried doing an absolute value but it didnt work the way I wanted. By sorting my data, it also moves my formulas, which is the problem.

Sheet is attached. Try to make C7 = 22. Then sort the data, the C5 will be changed, which I dont want it to do. I like to keep it a table because as the user adds new rows, it will populate the chart automatically.

Capture.PNG

Thank you!

make cell c5 the following formula

Please Login or Register  to view this content.

and then copy down to all table cells in that column

sorting by column B will no longer affect your cumulated totals. all formulas adapt themselfs automaticly

hahaha I cant believe I didnt think of that! Thank you both, both ways worked.