Board index » Excel » Refreshing AutoFilter in Linked Worksheets

Excel44
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. :)





--

EonZ

------------------------------------------------------------------------

EonZ's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36273

View this thread: http://www.excelforum.com/showthread.php?threadid=560557


-

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





"EonZ" <EonZ.2atj20_1152684003.2486@excelforum-nospam.com>wrote in message



-

That helped a lot! Thank you so much.





--

EonZ

------------------------------------------------------------------------

EonZ's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36273

View this thread: http://www.excelforum.com/showthread.php?threadid=560557



-

Any ideas on the previous reply?



--

Eon

-----------------------------------------------------------------------

EonZ's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3627

View this thread: http://www.excelforum.com/showthread.php?threadid=56055



-