Row wont lock on sort from linked column on another sheet

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.

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

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.

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

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?

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?

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.

Please Login or Register  to view this content.

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.