1

I have a table in an Excel sheet that has all my data (let's call it Table A). I want to use AutoFilter on this table and display only what's visible on a different table (Table B) on a different sheet. Table B only has a few columns from TableA - it does NOT have a month or date column.

How can I set it up so that when I filter Table A, the Table B on a different sheet is also filtered?

Note: removing the non-visible rows from TableB is an option, but I don't know how I would do that.

elliot svensson
  • 593
  • 3
  • 11
SUMguy
  • 1,505
  • 4
  • 31
  • 61
  • 1
    I really think that the answer I gave below will do what you need it to do, if you apply it. Would you like some steps for implementing it? – elliot svensson Mar 27 '18 at 16:04
  • 1
    could you have one table as actually being a pivottable showing only the columns you are interested in? You could format that to pretty much look like a standard table and have the page field as your month for filtering. Then use the pivottable update event to filter your table in the other sheet? Method is described here: http://blog.contextures.com/archives/2013/05/14/use-slicers-to-filter-a-table-in-excel-2010/ and it pretty easy. – QHarr Mar 27 '18 at 16:58
  • 1
    The other method I was thinking of is quite "expensive" which was more like this https://stackoverflow.com/questions/15904230/vbatrigger-macro-on-column-filter And there is a SO user who wrote a "slave-table" sub but I can't remember his handle at present. – QHarr Mar 27 '18 at 16:59
  • 1
    @jeffreyweir's answer https://stackoverflow.com/questions/49339985/excel-extract-a-column-from-a-pivot-table-and-show-it-in-a-regular-table/49341702#49341702 may be a starting point. – QHarr Mar 27 '18 at 17:14

1 Answers1

1

There are not too many ways for Table B to know whether a row in Table A is visible. One way is the SUBTOTAL function's "103" mechanism.

Follow these steps to use the =SUBTOTAL(103, ...) function on Table B to check if the corresponding row at Table A. Then filter against this result so that filters at Table B match what's done at Table A:

1) Identify or make a unique column in TableA and TableB which identifies a given row. If you don't have one, make a column that numbers the data from 1 to the number of rows. We'll say this is column A.

Make sure that the numbers identify the SAME DATA POINT in both tables... i.e. if 31 is January 2009 on Table A, then 31 needs to be January 2009 on Table B.

2) Add a new column in TableB with this formula in row 2 (because I wrote "A2" in the formula), and fill down: 

=SUBTOTAL(103, INDEX(**SELECT COLUMN A IN TableA**, MATCH(A2, **SELECT COLUMN A IN TableA [again]**,0)))

3) Filter TableB by the column you just created: 1 = shown, 0 = hidden.

Done!

elliot svensson
  • 593
  • 3
  • 11