Excel Data validation won't sort based on column value

Question

I have a simple absence schedule file, in sheet DATA column B there are some names of co-workers (or letters in this case..) in sheet JAN column C those names appear again (if a 1 is selected in sheet DATA column C) but that's not the point.

Problem: those names have to be sorted sometimes (when new one's appear) but the DATA VALIDATION columns in sheet JAN don't sort with them (see colors in rows) I don't want to use VBA because i don't know it, so hopefully there is a formula solution (use formula in the data validation popup? something with INDIRECT?)

Hopefully someone can't help me out with this.

TEST FILE

As in picture below, left side is the DATA SHEET where there are some names filled in, on the right side is the JAN SHEET where those names are displayed with an IF formula, when we sort the names in DATA SHEET they will sort in JAN SHEET as well, but the DATA VALIDATION in JAN SHEET from column D tot H isn't changing with it. I don't want to solve it with VBA because i don't know it, so thought to fix it with a formula, but can't find a solution.. read something that you can add a formula to the data validation list popup (INDIRECT...).

Answer 1

In the Jan sheet you are referencing the cells in the Data sheet with a formula and then you have formulas and enter values in the columns next to the cell reference. If the data in the Data sheet changes and gets re-ordered, the cells in Jan, column D will show the new sort order, but you need to understand that this does not affect all the data in the columns to the right. You only sorted the DATA sheet, but not the Jan sheet.

This is a flaw in the data architecture of the workbook. Don't use cell references to the data sheet for the names and for the formulas in D to J. The cells in columns D to J have formulas that refer to the DATA sheet, as well as data validation drop-downs. That is a dangerous thing to do. If the sort order of the DATA sheet changes, the cells that still use a formula will refer to the rows, but the cells where a value has been picked from the list may not match up with the correct name, as you have found out.

I suggest you copy and paste the names from DATA to Jan. Then use a formula that calculates the value based on a Vlookup with the Name as the lookup value.

If a new name gets added to the DATA sheet, you need to manually copy and paste that name to the Jan sheet.

Now you can sort the Jan sheet by column C. The formulas will show the correct value because they will look it up based on the name. The cells with a value picked from the data validation will travel with the sort if the whole range is selected before sorting.