adding cells then sorting

[Helen B]

Hi

I need to add specific cells in a column, from one worksheet into another,
by department. The easiest way I have found to do this is to sort the sheet
by department then add the cells for each department, but I need to resort
the sheet by surname.

example: sheet2!B9 =sheet1!F11+sheet1!F12+sheet1!F13+sheet1!F14+sheet1!F15

the only problem is when I resort sheet1 by surname the cells added in
sheet2 remain the same (as above), when I want the cells added to also be
resorted (as below).

example: sheet2!B9 =sheet1!F15+sheet1!F9+sheet1!F26+sheet1!F12+sheet1!F5

I hope this makes sense, I have tried using relative, absolute and mixed
references but nothing works.

Please help

Helen

 

[smartin]

Hi

I need to add specific cells in a column, from one worksheet into another,
by department. The easiest way I have found to do this is to sort the sheet
by department then add the cells for each department, but I need to resort
the sheet by surname.

example: sheet2!B9 =sheet1!F11+sheet1!F12+sheet1!F13+sheet1!F14+sheet1!F15

the only problem is when I resort sheet1 by surname the cells added in
sheet2 remain the same (as above), when I want the cells added to also be
resorted (as below).

example: sheet2!B9 =sheet1!F15+sheet1!F9+sheet1!F26+sheet1!F12+sheet1!F5

I hope this makes sense, I have tried using relative, absolute and mixed
references but nothing works.

Please help

Helen

Instead of (re)sorting, try using Autofilter. In Excel 2003 this is
found under Data | Filter | Autofilter.

Or, use sorting, but after finding the values with formulas, copy the
results and Edit | Paste Special | Values.