Sorting question

[edward/]

I may have posted this in the wrong section, so please bear with me.

I have created a spreadsheet that analyzes component life for use at my
work. Eventually, I would like all employees to be able to use this
spreadsheet.

The spreadsheet looks at the hours individual components have on them and
then calculates an estimated amount of time until a particular component
needs to be changed. This is done using (hours remaining/avg. operating hours
per month).

The spreadsheet is set up so that the user updates the operating hours on
the machine and then all the component hours, life remaining and estimated
replacement time are all updated from this input.

I would like the spreadsheet to automatically sort the component data
columns by estimated replacement time every time the spreadsheet is updated.
It needs to sort the column that contains the estimated time remaining as
well as bring other data and formulas as well so that formulas dont get
messed up when data is moved around.

Is there a rule that I can set up or maybe create a button that the user
could press to automatically sort the columns?

Hopefully I have explained my problem well enough. If not, let me know and
Ill try to make it more clear.

Thanks.

 

[Joel]

One method is to remove the formiulas by copying and the pasting back to the
same location in the worksheet using PasteSpecial - Values. Then perform the
sort. I fyou need the formulas then use a temporary worksheet and copy the
sheet to the temporary sheet using PasteSpecial - values and sort on the
temporary sheet.

 

[edward/]

The formulas will need to be used each time the input data is changed.

Let me give an example of what I need to happen.

Suppose component1 is a part one machine1 and component2 is a part on
machine2.

Now lets say that coponent1 has 16,000 hours on it and component2 has 16,500
hours on it. They would be sorted so that component2 would be below
component1 in the column.

Next, a user updates the hours on the machines and adds 1,500 hours to
machine1 (and component1) and 250 hours on machine2 (and component2). The
hours added to the machine are also added to the component via formulas.
With this new data, component2 would now have 250 hours less than component1,
but component1 would still be on the top of the list.

I would like the spreadsheet to automatically resort the information after
the user has updated the machine hours.

Is there a way to do this?

 

[Joel]

The best way is to have a master worksheet and a sorted worksheet. Then run
a macro which clears the sorted sheet, copies the master to the sorted using
Pastespecial values, and then sorts the copied data.

 

[Bernd P]

Hello,

You do not even need a macro:
http://sulprobil.com/html/sorting.html

Regards,
Bernd