Sorting rows/columns and keeping reference!

M

minismood

Hello!

I have a worksheet with about 20 sheets or something and in every sheet
I have information that every now and then gets updated. The
information from the 20 sheets is summarized in a separate sheet.

The information in one column contains a number with some text, i.e.
112200 Project 2, and the amount of hours in another column next to the
project.

The problem is that the information in every sheets gets updated with
new project and I want to sort them after the project number I can´t
do that because then in the summarized sheet the sum for that
particular project is wrong because i.e. 112200 Project 2 has swiched
its cell reference..

Can you somehow bookmark a particular number or something so you can
change the position of a project but not lost the sum?

Can someone understand my rubbish? :)

Please help me!
 
P

Pete_UK

I think I understand, and I think the best approach is to put your
total formulae somewhere where they do not get affected by the sort. In
your project sheets can you put the total on the top line, so it might
look like:

=SUM(D3:D50)

refering to values in column D, although the formula may be in M1
(allowing headings in row2)? Then in your summary sheet you would have
the formula:

=project_sheet!M1

and this would be unaffected by any sorting which you may do on
project_sheet.

Hope this helps.

Pete
 
Top