Refreshing AutoFilter in Linked Worksheets

E

EonZ

Here's my dillema:

I have 2 worksheets which have the identical data. The second worksheet
is completely linked to the first worksheet:

For example, the value for A1 in the second worksheet is =Sheet1!A1.

The second sheet is supposed to show values in the "Rank" Category that
are only equal to 5. The first sheet has about 100 "Rank" items listed
from 1-5. Sheet 2 is autofiltered to only show values equal to 5.

The problem arises when I add new items in Sheet 1 with a "Rank" of 5
and those items are not showing up in Sheet 2. But when I click the
drop down and select "(All)," then the values show up. Basically, Sheet
2 doesn't automatically refresh after I add a new value to Sheet 1.

I want to know if there is a workaround for this? PLEASE HELP!

Thank you. :)
 
A

Arvi Laanemets

Hi

Let's assume rank items in Sheet1 are in column A, and you don´t have any
headers in sheet A (as follows from your message), you have 2 additional
columns (Col1 and Col2) in your table, and the number of filled rows in
Sheet1 is less than 100.

Add a column to left of your table in Sheet1 - it will be new column A now.
Into Sheet1!A1 enter the formula like
=IF(B1="","",B1 & ":" & COUNTIF(B$1:B1,B1))
, and copy the formula down for at least for all rows in your table.
Hide added column.

On sheet Sheet2, p.e. to cell A1 apply data validation list with choices 1
to 5 (ranks). Select a value for a rank.
Into range A3:B3 enter headings ("Col1", "Col2")
Into cell A4 enter the formula (adjust lookup range to real one)
=IF(ISERROR(VLOOKUP($A$1 & ":" &
ROW()-3,Sheet1!$A$1:$D$100,3,0)),"",VLOOKUP($A$1 & ":" &
ROW()-3,Sheet1!$A$1:$D$100,3,0))
Into cell B4 enter the formula
=IF(A4="","",VLOOKUP($A$1 & ":" & ROW()-3,Sheet1!$A$1:$D$100,4,0))

Copy both formulas down for some reasonable amount of rows.

Now, when you select any rank value in Sheet2!A1, according rows (except
rank column) from Sheet1 are displayed immediately.


Arvi Laanemets
 
Top