dynamic assigning cell to another cell

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,664
Office Version
  1. 365
Platform
  1. Windows
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])
namestoreitemprice
lindaeasttv662
jackeastdryer444
alexeastcomputer723
alleneasttv356
maryeastdryer792
samnorthwasher452
adamnorthtv998
davidnorthdryer307
ericnorthcomputer464
jimsouthdryer410
timsouthcomputer890
tomsouthwasher899
suesouthtv931
lizsouthdryer739
danawestcomputer459
annawesttv967
kimwestdryer931
louiswestcomputer481
jeffwestwasher737
Total=SUBTOTAL(101,[price])

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

Some videos you may like

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
Joined
Aug 18, 2015
Messages
10,739
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
Joined
Mar 27, 2016
Messages
1,664
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
10,739
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
Joined
Mar 27, 2016
Messages
1,664
Office Version
  1. 365
Platform
  1. Windows
Thank you very much for all your help. The array solution is good enough. Thanks once again. I appreciate it.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top