Sorting a spreadsheet that uses data validation in a couple of columns.

[TaraMayFlanagan]

Input sheet: "Data" tab

Output sheet: "2023–24 Platinum Club" tab

I'm working with an output spreadsheet that's pulling data from an input spreadsheet in the same workbook. Some columns on the output sheet (columns C through G in the attached screenshots) are pulling content from the data sheet using basic formulas. Other columns (columns B and I in the attached spreadsheet) are pulling content from the data sheet using data validation in a drop-down list format.

My problem occurs when I try to sort the output spreadsheet by anything other than Member > Smallest to Largest, the the data validation formula used to populate the content in column I (Equipment) takes on the row number of the new location, which makes that equipment dropdown list no longer match the corresponding Member number in column C (Member).

For example, Row 4 on the output sheet contains data for Lock 408, Member 1.1, which corresponds to Row 4 on the input sheet. When the output sheet is sorted by Member > Smallest to Largest, the data validation formula in the Equipment column for member 1.1 is =Data!$K$4:$04 (this is correct). If I then resort the output sheet by Status > A–Z, the data validation formula in the Equipment column for member 1.1. changes to the new location based on whatever the current status of member 1.1 is, such as =Data!$K$5:$05 or =Data!$K$128:$0128. This means the equipment cell no longer matches the equipment data for member 1.1 but instead displays the equipment data for whichever member corresponds to row 5 or row 128, respectively.

We need to be able to sort the output spreadsheet (2023–24 Platinum Club) status without losing the member continuity as it displayed in the input spreadsheet (Data). The attached spreadsheet is a sample, so I need to be able to reproduce whatever the fix is on the live sheet. Any help you can provide would be greatly appreciated!

[#2]

@TaraMayFlanagan 

Unless I'm mistaken, those are the real names and real phone numbers of real people. You need to remove that link. I've alerted the forum moderator, but if you get to it first, you should remove it. Come back with a version or sample with fake names.

[#3]

@TaraMayFlanagan There are a number of things I would do differently with this spreadsheet; however, to deal with the question at hand, try using either one of the following lookup formulas as the data validation list source:

=INDEX(Data!$K$4:$O$1000, MATCH(C4, Data!$C$4:$C$1000, 0), 0)

- OR -

=XLOOKUP(C4, Data!$C$4:$C$1000, Data!$K$4:$O$1000)

The formulas shown above will return the corresponding row (from column K thru O on the "Data" worksheet) where the "Member" number is found in column C.

Note: if the XLOOKUP function is not available in your version of Excel, use the INDEX / MATCH method instead.

[#4]

This issue has been resolved elsewhere, thank you! Please remove the post if possible.