Conditional Formatting and ordering even maybe ?

pantsmonkey

Board Regular
Joined
Jul 2, 2002
Messages
109
Guys I have this sheet

test.jpg


It has 2 seperate groups each get a certain amount of force out per month.

I update the numbers daily but because they keep jockying for position the totals on the right hand side get all messed up.

I need a total sum of anything with TMS in on cell and RACM in the other ?

And if when I changed the numbers of each individual consultant it would re-rank them automatically that would be badass but that might be hard im not sure.

cheers[/img]
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
To get "TMS"
=sumif($B$2:$B$20,"=TMS",$C$2:$C$20)

To rank:
Press Alt-F11
Double click on the sheet in the left pane.
In the right pane, paste this:

Private Sub Worksheet_Change(ByVal Target As Range)
if target.column = 3 then
ReturnSpot = ActiveCell.Address
Columns("A:C").Select
Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range(ReturnSpot).Select
end if
End Sub
 

pantsmonkey

Board Regular
Joined
Jul 2, 2002
Messages
109
Both of those work Awesome Many thanks but I cannot undo changes that I do ?

I was testing it by chucking in random numbers and I cannot CTRL Z the updates is this a limitation of the VB editor or can I update it ?


Thanks again.
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
Once you have sorted, you can't undo the changes.

Perhaps you should just sort manually when you are done.
 

Forum statistics

Threads
1,147,844
Messages
5,743,512
Members
423,800
Latest member
IuneKeiki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top