dynamic assigning cell to another cell

[lezawang]

Hi
I have table like below. Cell B2 = B9. I want store location to be shown in B2 after I filter the main table. So if I filter South, then I want B2= South, if I filter North, then I want B2= North

Right now, it does not work, it always shows "East". Any idea how I can solve this? Thank you so much?

store	=B9
average	=SUBTOTAL(9,D9:D27)
sum	=SUBTOTAL(9,Table1[price])
count	=SUBTOTAL(2,Table1[price])
max	=SUBTOTAL(4,Table1[price])
min	=SUBTOTAL(5,Table1[price])
name	store	item	price
linda	east	tv	662
jack	east	dryer	444
alex	east	computer	723
allen	east	tv	356
mary	east	dryer	792
sam	north	washer	452
adam	north	tv	998
david	north	dryer	307
eric	north	computer	464
jim	south	dryer	410
tim	south	computer	890
tom	south	washer	899
sue	south	tv	931
liz	south	dryer	739
dana	west	computer	459
anna	west	tv	967
kim	west	dryer	931
louis	west	computer	481
jeff	west	washer	737
Total			=SUBTOTAL(101,[price])

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Eric W]

You could use something like this array formula in B2:

=INDEX(Table1[store],MIN(IF(SUBTOTAL(103,OFFSET(INDEX(Table1[store],1),ROW(Table1[store])-ROW(INDEX(Table1[store],1)),0)),ROW(Table1[store])-ROW(INDEX(Table1[store],1))+1)))

confirmed with Control+Shift+Enter.

It also seems to me that you might be able to use Advanced Filtering. If you set it up right, it might be possible so that you manually type in the store you want in B2, and that triggers the filtering. I'd have to work out the details though. If it's of interest, let me know.

 

[lezawang]

Thank you so much for the help. I would love to know the Advanced filter solution. It is easier to understand than the array function for sure. Thank you once again. I really appreciate it.

 

[Eric W]

After playing around with Advanced Filter, it doesn't really work the way I hoped. First, it seems to have some issues with using a table as the range. Second, it's not dynamic. Which is to say, you set up the table and criteria, then run AF, and it filters your list. But you can't just change the criteria and have it rerun, you need to change the criteria, then run the AF steps again. Not real friendly. You could simulate that with a macro. Have an event handler monitor your B2 cell, and if it changes, filter the table automatically. Let me know.

 

[lezawang]

Thank you very much for all your help. The array solution is good enough. Thanks once again. I appreciate it.