Row wont lock on sort from linked column on another sheet

[stevesince1981]

Good morning,

This is my first post, and I have searched the forum pretty well.

The two sheets in question are ‘Employee Totals’ and ‘PP19’

The column “Name” in PP19 is linked to the column “Name” on Employee Totals via
='Employee Totals'!B5

My question is, if I sort Name on Employee Totals, it sorts Name of PP19. However, the row associated with the sort does not change. So if I have data on PP19 for Test 1, if I sort, the data stays with the row but Test 1 will move to the appropriate sort without the associated data.

Question 1: How can I lock the row to move with the sort?

Question 2: With regards to filters, I understand how to manually select filters, but how can I pass the value of a filter from a drop down box as included in the Search By box on PP19? Would it require VBA?

Thank you in advance.

Side note: I dont want to use a table because I plan on sharing this among multiple users on a shared network to be updated at the same time.

[stevesince1981]

Update:
Been working and got my filter to work on PP19.

Still wondering if it is possible to sort Employee Totals to maintain the data row on PP19. If I sort employee totals, it sorts but wont maintain the row values.

Any thoughts?

[noboffinme]

Hi stevesince1981,

Is the issue that the Column 'Shift' value doesn't move with the relevant Column 'Name' in the PP19 Wsheet when you sort Employee Totals wsheet, Column 'Last, First' ?

I just want to clarify this first, Thanks

[stevesince1981]

Thank you for taking the time to respond.

I am removing all the data but two entries to make it easier for me to explain.

The Name, Shift, and Totals transfer fine. The user entry data under the days of the week for AUO and PT will not move in the row.

I am basically trying to lock row 7 with Andrews, Frank on Shift 3 with all his AUO/PT hours. But if you sort from the main page, it will move the Name, Shift, and Totals but not the AUO/PT. In turn, the Totals will be correct on the Master Totals sheet, but will be in the wrong place on the PP19 sheet.

Is there another way to refer to the worksheet to get the row data to stay intact on a sort? Or am I going about this wrong?

[noboffinme]

Hi Steve,

I'm working on the way to incorporate the 'Custom Sort' feature to run when you make changes to another worksheet.

If you needed just to do a sort on a range of cells on the SAME worksheet, you could select ALL the Columns & Rows >> go to Custom Sort >> & select the Column you want to sort by - this will sort ALL Rows by the Column you've selected.

The issue is that you want this to happen from another Worksheet - Next step is to get this to automatically activate once you sort the 'Employee Totals' Column & then get your Formulas to be 'sortable' as they will also move during this sorting process.

Can I clarify a few questions;

1. You firstly Sort by which exact Columns together on the 'Employees Totals' wsheet?
2. The effect you want is the Corresponding name on PP19 to keep its other Row data during this sort process?
3. Your Formulas may be affected during this sort or you had a solution for this?

All of this possible, don't worry.

[stevesince1981]

Let me answer your questions below.

1.You firstly Sort by which exact Columns together on the 'Employees Totals' wsheet? "Last, First" (C3), "AUO" (E4), "PT" (F4)
2.The effect you want is the Corresponding name on PP19 to keep its other Row data during this sort process? Correct. To keep the same order and allowing all the AUO/PT non calculated fields to move with the calculated linked fields. For the first row, it would include C7:AG7
3.Your Formulas may be affected during this sort or you had a solution for this? the formula hasn't changed as of yet. On my employee Totals, I am referencing the PP19 totals by using ="PP19'!AK7

I am trying to make the process automtaed; click of the button to sort based on last name, click of the button to sort on shift. I hope this is possible.

[noboffinme]

OK, I'm trying to get my head around how you're going to use this, so let me know if I'm correct here on how you will use this workbook.

The various 'PP...' Worksheets collect info (Hours or work?) by Staff Name & Shift Type (Admin, Bikes, Detailer etc) - To do this, someone enters the Daily figures & Shift Type for each Name

The Weekly Totals from the 'PP...' Worksheet are referenced to the 'Employee Totals' Worksheet to show a summarised view by Name & Shift Type.

You want to have a Filter on the 'Shift Types' on the 'Employee Totals' Worksheet, so you can quickly see a summarised view for the Weekly Totals.

For all of this to be correct, the 'PP...' Worksheets figures need to be in synch with the Names on the 'Employee Totals' Worksheet in case they are sorted.

You also want to be able to sort by the 'Shift (Type)' on the 'Employee Totals' Worksheet as well as the Name & also have the Figures in synch.

Is this what you want?

If so, the filter for the 'Shift Type' should appear on the 'Employee Totals' Worksheet & not Cell 'B2' of the 'PP19' Worksheet OR am I missing something?

Can you advise of the above as it all affects the most efficient way to build the requirements, Cheers

[stevesince1981]

I’ll give you a scenario…

Say I have 100 employees on the Employee Total list that has been presorted A-Z. And in a month, I decide to add 10 new employees to the list. The 10 new employee woululd start somewhere in row 107ish. So the manager clicks on PP19, clicks to Search By and selects Shift 3. Say there are 50 employees in Shift 3 , the first half are already presorted based on the initial entry but at the very bottom, there are 4 new names there were added that are not sorted.

My question is, how would I sort the master list, to link with the PP19 to keep the row data in line with the employee. As of now, if I were to sort, the row data on PP19 doesn’t move with the associated Employee from the Employee Totals.

Additionally, I am working on another post that generates an error when I protect the sheet, and share workbook. It basically turns off my Search By on PP19 and doesn’t allow the vba to work. If anything, I am more concerned about the vba error on the shared workbook than the sort. I want the document to be shared among multiple users at the same time to updated as needed. It seems that I cannot protect and unprotect the data while shared?

http://www.excelforum.com/excel-prog...or-1004-a.html

Any assistance on either would be greatly appreciated.

[noboffinme]

Getting closer to what you need now.

I wonder whether it would be better to have the info, 'Names, Shift, etc added to the 'PP...' Worksheet first & the 'Employee totals' Worksheet updates from it using the 'VLOOKUP' Function?

A sort on the 'PP...' sheet would then update the 'Emp totals ws. Would that work for you?

So you effectively don't update 'Emp Totals' at all but can filter the Names, Shifts etc from it.

Re your second question about Protection & Macros clash, Why are protecting the Worksheet? to protect overwriting of Formulas or some other reason?

[stevesince1981]

I guess the reason for the names being housed on Emp Totals comes from the need of having multiple pp's. PP1, PP2, PP3, etc.



I figured out another solution for the second half of the question with the vba after playing with the coding for a bit. The sheet still needs to be protected because of the formulas.

[noboffinme]

OK I can sort the Rows based on the 'Employee Totals' Names & Shift.

This will sort in A-Z order & the names MUST be on the other 'PP...' Worksheets.

How are the names added to the 'PP...' Worksheets?

[noboffinme]

Hi Steve,

Sorry for the delayed response to your question.

Below is the code to sort the eentirerow on another worksheet but it would present an issue if the names weren't the same on each sheet.

Sub sort_entirerow()

Sheets("PP19").Select
Cells.Range("A7:AL2000").Select
    ActiveWorkbook.Worksheets("PP19").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("PP19").Sort.SortFields.Add Key:=Range("A7:A2000"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal        'Set to Sort on Column 'A' FROM 'A7' down to 'A2000'
    With ActiveWorkbook.Worksheets("PP19").Sort
        .SetRange Range("A7:AL2000")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

I've also attached a workbook that has the Excel Formula that enables sorting of the calculating formula as each formula is the same.

Check out Cell 'Q7' in wsheet PP19.

I need more info on how your file is going to work to help further.