# dynamic assigning cell to another cell

#### lezawang

##### Well-known Member
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>

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Eric W

##### MrExcel MVP
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

##### Well-known Member
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

##### MrExcel MVP
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

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

Replies
2
Views
328
Replies
2
Views
224
Replies
2
Views
200
Replies
3
Views
201
Replies
3
Views
531

1,126,934
Messages
5,621,688
Members
415,851
Latest member
JohnZ1156

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back