Working Excel - Need help

[ExcelNovice]

This forum is a good thing.
I know a genius will solve this problem in no time.

Cells E3:E6 are populated with numbers, so too are cells F3:F6.
First I want to auto sort the following range B3:F6, sorting by E3:E6 whenever a value in E3:E6 is changed.
Second I want to auto sort the following range B3:F6, sorting by F3:F6 whenever a value in F3:F6 is changed.

Then, I want to do this: Using macro (or any other way possible), determine if E3>E4 and if so, place D3 in cell H3 and D4 in cell H4.
If E3=E4, then determine if F3>F4 and if so then place D3 in H3 & D4 in H4.
If F3
That's it!
I hope this is not too crazy. Thanks in advance.

[Yogi Anand]

On 2002-05-13 17:13, ExcelNovice wrote:
This forum is a good thing.
I know a genius will solve this problem in no time.

Cells E3:E6 are populated with numbers, so too are cells F3:F6.
First I want to auto sort the following range B3:F6, sorting by E3:E6 whenever a value in E3:E6 is changed.
Second I want to auto sort the following range B3:F6, sorting by F3:F6 whenever a value in F3:F6 is changed.

Then, I want to do this: Using macro (or any other way possible), determine if E3>E4 and if so, place D3 in cell H3 and D4 in cell H4.
If E3=E4, then determine if F3>F4 and if so then place D3 in H3 & D4 in H4.
If F3
That's it!
I hope this is not too crazy. Thanks in advance.

1) For the first part you need a VBA
solution

2) for the seconfd part, try the following formulas

in cell H3 ... =IF($E$3>$E$4,D3,IF($E$3=$E$4,IF($F$3>$F$4,D3,"whateverElse")))
in cell H4 ... =IF($E$3>$E$4,D4,IF($E$3=$E$4,IF($F$3>$F$4,D4,"whateverElse")))

I am sure these can be simplified, but this will get you started.

[ExcelNovice]

Thanks man. I'm willing to give it a shot. Let's see if someone else can give me some more ideas.

[NateO]

I tried to crank the whole nine yards in vba. Right click on the sheet tab in question, click 'view code' and paste the following:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cl As Range

Application.EnableEvents = False

If Not application.Intersect(Target, [e3:f6]) Is Nothing Then

If Not application.Intersect(Target, [e3:e6]) Is Nothing Then _

[b3:f6].Sort Key1:=Range("E3"), Order1:=xlAscending, Header:=xlGuess

If Not application.Intersect(Target, [f3:f6]) Is Nothing Then _

[b3:f6].Sort Key1:=Range("f3"), Order1:=xlAscending, Header:=xlGuess

If [e3].Value > [e4].Value Then

    [h3].Value = [d3].Value

    [h4].Value = [d4].Value

ElseIf [e3].Value = [e4].Value Then

    If [f3].Value > [f4].Value Then

        [h3].Value = [d3].Value

        [h4].Value = [d4].Value

    End If

End If

End If

Application.EnableEvents = True

End Sub

This does a sort, in ascending order, not sure which way you wanted to go, but if need be (much like my poor dog) it can be fixed.
_________________
Cheers, NateO

[ This Message was edited by: nateo on 2002-05-13 19:19 ]

[dimrod]

You see. It didn't need any geniuses.

[ExcelNovice]

You're all geniuses.

By the way NateO, I place the code in as you suggested but I cannot get it to work. Is there something else that I need to do? Even when the cell values change nothing happens.

Sorry for the trouble.

[dimrod]

On 2002-05-13 17:56, ExcelNovice wrote:
You're all geniuses.

The one-eyed man in the land of the blind..... etc.

[NateO]

No trouble. You did indeed get the code in the correct sheet module as described above (Right click on the sheet tab (sheet with data), click 'view code' and paste the following)....Perhaps your events aren't enabled. Put the following in any normal module, run it and try again:

sub ck()
application.enableevents = true
end sub

Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-13 18:25 ]

[ExcelNovice]

Call me stupid, but I just can't get this thing to work. I did everything you said...then I realize the problem:

The macro is running but only if I change the value in the cell by clicking on the cell and entering new values. That's not what I'm doing. The value in the cells are actually changing when I change the values in cells on a totally different sheet.

I thought the macro would run when the value is changed, nevermind the fact that the change is not done directly. Guess I just don't know this thing.

Nateo can you help please?

[NateO]

ExcelNovice, did you notice my private message way back when? I'd be more than happy to help. I could show you one of a few ways to accomplish this. If you're still interested, to make this more fruitful, name the worksheet that the entry is made, and the range the entry is made, and the name of the sheet where the data is manipulated.