Linking Rows Together When Cells Can Change

Can you explain your problem further?

The sheet seems to do what you are asking for.

What am I missing?

Hi, the problem is, when I change the order of names, the values in the Week columns in ACW don't.

I'm assuming then that you mean the values in Week 1 in your sample.

Running your macro fails because Sheet "Cumulative" is missing, post a sample that has this sheet and I'll look at it again.
It seems to me that you might need to trigger this macro in the sheet change event, but until I see it run successfully I can't be sure about what it does.

Hi, I've not had a cumulative post in quite a while, I changed this to Summary which is included in the attached workbook.

Honestly not sure where the Macro is from, I'm just starting to learn about Macros

I'm having broadband connection issues, BT b*****ing about again, I'll get back to you as soon as I can get a connection long enough to download your new file.

Bear with me unless someone else helps.

Thanks Marcol

Hello, can anyone help with this please? I've been trying buy still can't figure out how to do it!

Hi, I know I haven't given enough information so I've added a bit more, please ignore any Macro in the example sheets as that was my failed attempt at trying to make one

Team Worksheet

First Name Surname
Alan A
Brian B

ACW Worksheet

First Name Surname Value
Alan A 1
Brian B 2

When I change the order of “Surname” in “Team”, this changes the order in “ACW” however not the value on the same row as the values so I end up with something like below.

First Name Surname Value
Brian B 1
Alan A 2

Is it possible to change this so that when I change the order of “Surname” in “Team” that it would show like below?

First Name Surname Value
Brian B 2
Alan A 1

Thanks, I'm sorry I didn't give enough information previously!

I assume you need to sort a range automatically, then use the following User defined function
or your refer to your file with changes made

Function SORTDATA(x As Range) As Variant
Dim n As Integer
n = x.Rows.Count
sortarray = x
Do
sortstop = True
For i = 1 To n - 1
If UCase(sortarray(i, 1)) > UCase(sortarray(i + 1, 1)) Then
sortstop = False
Temp = sortarray(i, 1)
sortarray(i, 1) = sortarray(i + 1, 1)
sortarray(i + 1, 1) = Temp
End If
Next i
Loop While Not (sortstop)
SORTDATA = sortarray
End Function

Sorry, attachemet missed in the previous post

I've looked at this several times and am still struggling to understand how the sheet is meant to work.

I see what you are trying to do, I think, but I'm at a loss as to how you see it finally needed to work.

What are the figures in ACW Week Nos based on? How are they associated with the employees' name?

If you could explain this then it should be possible to do what you need without macros, or a least reduce them to change events.

Your problem should not be difficult, it's just a question of seeing it clearly.

Cheers

The ACW Week Nos are based on After Call Work. Each Employee has 52 weeks where this is recorded. The problem I have is I can sort the names on the Team sheet and this changes the order of the names on the ACW sheet, however, the ACW values for each advisor does not change

As you can see from the example above, when I change the order from A-Z to Z-A, Brian B now has Alan A's Stat, the values do not change order with the names.

I'm not really sure how to explain it any other way to be honest!

Thanks,

Sorry for the late reply. That works great, thanks!

I do have a wee issue with doing the same thing to the summary screen. It removes the formulas in Summary when run.

Could you have a wee look and tell me what's wrong?

For Summary!D7
=INDEX(ACW!$D$6:$BC$24, MATCH($B7&"-"&$C7, INDEX(ACW!$B$6:$B$34&"-"&ACW!$C$6:$C$34, 0), 0), $C$3)