Referencing a cell in a list

[kcjaries78]

I have a simple table of data and I've used Auto Filter. I sort the data with a list based on who owns the data. I want to be able to take who I selected the list to sort by, and make it appear in a cell that is part of the title. So if I sort the list by Carol, I want Carol to appear in a specific cell that is part of the title. When I select Yvonne, I want Yvonne to appear in that cell instead. If I say =(M5), M5 is not always the first cell. The list might change it to M75 when it's all sorted, so it doesn't work. Any suggestions?

 

[Aladin Akyurek]

I have a simple table of data and I've used Auto Filter. I sort the data with a list based on who owns the data. I want to be able to take who I selected the list to sort by, and make it appear in a cell that is part of the title. So if I sort the list by Carol, I want Carol to appear in a specific cell that is part of the title. When I select Yvonne, I want Yvonne to appear in that cell instead. If I say =(M5), M5 is not always the first cell. The list might change it to M75 when it's all sorted, so it doesn't work. Any suggestions?

Control+shift+enter, not just enter:

=INDEX(M5:M22,MATCH(1,SUBTOTAL(3,OFFSET(M5:M22,ROW(M5:M22)-ROW(M5),,1)),0))

 

[kcjaries78]

That was quick. I adjusted my M cell values to fit, But it errored on me. If I want the description in cell H2 would it look different?

 

[kcjaries78]

Sorry, I figured out the problem, the cell was merged. That's great. Thanks.