paste link and filter

[snayar]

I am trying to link a subset of a master set of data to another worksheet. I can paste link the entire data table and then autofilter to get just the data on want on the other worksheet, but if I then change the data on the 'master' sheet, then the autofilter doesn't automatically update unless I hit the drop down again. Is there some sort of formula I can use to accomplish what Im trying to do so that if I change the master sheet, the other worksheet with the filtered subset will also automatically add/delete that record?

Thanks in advance for the help.

[Chris Davison]

Snayar,

check your email.... I am unable to post a "screenshot" which explains it a bit easier so have emailed you an example.....

when my HTML file works again, I'll repost

[snayar]

Thatks a bunch. this works great. I actually muddled my way through it with pivot tables (probably the way I should have gone in the first place).

But I appreciate the assistance. Thanks!

[Chris Davison]

Microsoft Excel - snayar.xls_______________Running: xl97 : OS = Windows (32-bit) 4.90
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
N8		=
*	A	B	C	D	E
1	concat	Name	Position	Height	Weight
2	:alert('=C2&COUNTIF($C$2:C2,C2)')>Centre1	John	Centre	1.8	90.5
3	:alert('=C3&COUNTIF($C$2:C3,C3)')>Midfield1	Paul	Midfield	1.9	90.8
4	:alert('=C4&COUNTIF($C$2:C4,C4)')>Attack1	George	Attack	1.7	90.6
5	:alert('=C5&COUNTIF($C$2:C5,C5)')>Attack2	Ringo	Attack	1.6	90.5
6	:alert('=C6&COUNTIF($C$2:C6,C6)')>Centre2	Hugh	Centre	1.5	90.8
7	:alert('=C7&COUNTIF($C$2:C7,C7)')>Midfield2	Pugh	Midfield	1.6	90.6
8	:alert('=C8&COUNTIF($C$2:C8,C8)')>Centre3	Barney	Centre	1.7	90.5
9	:alert('=C9&COUNTIF($C$2:C9,C9)')>Attack3	McGrew	Attack	1.8	90.8
10	:alert('=C10&COUNTIF($C$2:C10,C10)')>Centre4	Cuthbert	Centre	1.9	90.6
11	:alert('=C11&COUNTIF($C$2:C11,C11)')>Midfield3	Dibble	Midfield	1.9	90.5
12	:alert('=C12&COUNTIF($C$2:C12,C12)')>Centre5	Grub	Centre	2.3	90.8
13	:alert('=C13&COUNTIF($C$2:C13,C13)')>Attack4	Jack	Attack	1.8	90.6
Master

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.20]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

[Chris Davison]

Microsoft Excel - snayar.xls_______________Running: xl97 : OS = Windows (32-bit) 4.90
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
N8		=
*	A	B	C	D	E	F
15	Centre	concat	Name	Position	Height	Weight
16	1	:alert('=$A$15&A16')>Centre1	:alert('=IF(ISNA(VLOOKUP($B16,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B16,$A$1:$E$13,COLUMN(B:B),0))')>John	:alert('=IF(ISNA(VLOOKUP($B16,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B16,$A$1:$E$13,COLUMN(C:C),0))')>Centre	:alert('=IF(ISNA(VLOOKUP($B16,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B16,$A$1:$E$13,COLUMN(D:D),0))')>1.8	:alert('=IF(ISNA(VLOOKUP($B16,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B16,$A$1:$E$13,COLUMN(E:E),0))')>90.5
17	2	:alert('=$A$15&A17')>Centre2	:alert('=IF(ISNA(VLOOKUP($B17,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B17,$A$1:$E$13,COLUMN(B:B),0))')>Hugh	:alert('=IF(ISNA(VLOOKUP($B17,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B17,$A$1:$E$13,COLUMN(C:C),0))')>Centre	:alert('=IF(ISNA(VLOOKUP($B17,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B17,$A$1:$E$13,COLUMN(D:D),0))')>1.5	:alert('=IF(ISNA(VLOOKUP($B17,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B17,$A$1:$E$13,COLUMN(E:E),0))')>90.8
18	3	:alert('=$A$15&A18')>Centre3	:alert('=IF(ISNA(VLOOKUP($B18,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B18,$A$1:$E$13,COLUMN(B:B),0))')>Barney	:alert('=IF(ISNA(VLOOKUP($B18,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B18,$A$1:$E$13,COLUMN(C:C),0))')>Centre	:alert('=IF(ISNA(VLOOKUP($B18,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B18,$A$1:$E$13,COLUMN(D:D),0))')>1.7	:alert('=IF(ISNA(VLOOKUP($B18,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B18,$A$1:$E$13,COLUMN(E:E),0))')>90.5
19	4	:alert('=$A$15&A19')>Centre4	:alert('=IF(ISNA(VLOOKUP($B19,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B19,$A$1:$E$13,COLUMN(B:B),0))')>Cuthbert	:alert('=IF(ISNA(VLOOKUP($B19,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B19,$A$1:$E$13,COLUMN(C:C),0))')>Centre	:alert('=IF(ISNA(VLOOKUP($B19,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B19,$A$1:$E$13,COLUMN(D:D),0))')>1.9	:alert('=IF(ISNA(VLOOKUP($B19,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B19,$A$1:$E$13,COLUMN(E:E),0))')>90.6
20	5	:alert('=$A$15&A20')>Centre5	:alert('=IF(ISNA(VLOOKUP($B20,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B20,$A$1:$E$13,COLUMN(B:B),0))')>Grub	:alert('=IF(ISNA(VLOOKUP($B20,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B20,$A$1:$E$13,COLUMN(C:C),0))')>Centre	:alert('=IF(ISNA(VLOOKUP($B20,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B20,$A$1:$E$13,COLUMN(D:D),0))')>2.3	:alert('=IF(ISNA(VLOOKUP($B20,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B20,$A$1:$E$13,COLUMN(E:E),0))')>90.8
21	6	:alert('=$A$15&A21')>Centre6	:alert('=IF(ISNA(VLOOKUP($B21,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B21,$A$1:$E$13,COLUMN(B:B),0))')>*	:alert('=IF(ISNA(VLOOKUP($B21,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B21,$A$1:$E$13,COLUMN(C:C),0))')>*	:alert('=IF(ISNA(VLOOKUP($B21,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B21,$A$1:$E$13,COLUMN(D:D),0))')>*	:alert('=IF(ISNA(VLOOKUP($B21,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B21,$A$1:$E$13,COLUMN(E:E),0))')>*
22	*	*	*	*	*	*
23	Midfield	*	Name	Position	Height	Weight
24	1	:alert('=$A$23&A24')>Midfield1	:alert('=IF(ISNA(VLOOKUP($B24,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B24,$A$1:$E$13,COLUMN(B:B),0))')>Paul	:alert('=IF(ISNA(VLOOKUP($B24,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B24,$A$1:$E$13,COLUMN(C:C),0))')>Midfield	:alert('=IF(ISNA(VLOOKUP($B24,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B24,$A$1:$E$13,COLUMN(D:D),0))')>1.9	:alert('=IF(ISNA(VLOOKUP($B24,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B24,$A$1:$E$13,COLUMN(E:E),0))')>90.8
25	2	:alert('=$A$23&A25')>Midfield2	:alert('=IF(ISNA(VLOOKUP($B25,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B25,$A$1:$E$13,COLUMN(B:B),0))')>Pugh	:alert('=IF(ISNA(VLOOKUP($B25,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B25,$A$1:$E$13,COLUMN(C:C),0))')>Midfield	:alert('=IF(ISNA(VLOOKUP($B25,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B25,$A$1:$E$13,COLUMN(D:D),0))')>1.6	:alert('=IF(ISNA(VLOOKUP($B25,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B25,$A$1:$E$13,COLUMN(E:E),0))')>90.6
26	3	:alert('=$A$23&A26')>Midfield3	:alert('=IF(ISNA(VLOOKUP($B26,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B26,$A$1:$E$13,COLUMN(B:B),0))')>Dibble	:alert('=IF(ISNA(VLOOKUP($B26,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B26,$A$1:$E$13,COLUMN(C:C),0))')>Midfield	:alert('=IF(ISNA(VLOOKUP($B26,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B26,$A$1:$E$13,COLUMN(D:D),0))')>1.9	:alert('=IF(ISNA(VLOOKUP($B26,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B26,$A$1:$E$13,COLUMN(E:E),0))')>90.5
27	4	:alert('=$A$23&A27')>Midfield4	:alert('=IF(ISNA(VLOOKUP($B27,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B27,$A$1:$E$13,COLUMN(B:B),0))')>*	:alert('=IF(ISNA(VLOOKUP($B27,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B27,$A$1:$E$13,COLUMN(C:C),0))')>*	:alert('=IF(ISNA(VLOOKUP($B27,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B27,$A$1:$E$13,COLUMN(D:D),0))')>*	:alert('=IF(ISNA(VLOOKUP($B27,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B27,$A$1:$E$13,COLUMN(E:E),0))')>*
28	5	:alert('=$A$23&A28')>Midfield5	:alert('=IF(ISNA(VLOOKUP($B28,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B28,$A$1:$E$13,COLUMN(B:B),0))')>*	:alert('=IF(ISNA(VLOOKUP($B28,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B28,$A$1:$E$13,COLUMN(C:C),0))')>*	:alert('=IF(ISNA(VLOOKUP($B28,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B28,$A$1:$E$13,COLUMN(D:D),0))')>*	:alert('=IF(ISNA(VLOOKUP($B28,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B28,$A$1:$E$13,COLUMN(E:E),0))')>*
29	6	:alert('=$A$23&A29')>Midfield6	:alert('=IF(ISNA(VLOOKUP($B29,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B29,$A$1:$E$13,COLUMN(B:B),0))')>*	:alert('=IF(ISNA(VLOOKUP($B29,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B29,$A$1:$E$13,COLUMN(C:C),0))')>*	:alert('=IF(ISNA(VLOOKUP($B29,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B29,$A$1:$E$13,COLUMN(D:D),0))')>*	:alert('=IF(ISNA(VLOOKUP($B29,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B29,$A$1:$E$13,COLUMN(E:E),0))')>*
30	*	*	*	*	*	*
31	Attack	*	Name	Position	Height	Weight
32	1	:alert('=$A$31&A32')>Attack1	:alert('=IF(ISNA(VLOOKUP($B32,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B32,$A$1:$E$13,COLUMN(B:B),0))')>George	:alert('=IF(ISNA(VLOOKUP($B32,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B32,$A$1:$E$13,COLUMN(C:C),0))')>Attack	:alert('=IF(ISNA(VLOOKUP($B32,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B32,$A$1:$E$13,COLUMN(D:D),0))')>1.7	:alert('=IF(ISNA(VLOOKUP($B32,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B32,$A$1:$E$13,COLUMN(E:E),0))')>90.6
33	2	:alert('=$A$31&A33')>Attack2	:alert('=IF(ISNA(VLOOKUP($B33,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B33,$A$1:$E$13,COLUMN(B:B),0))')>Ringo	:alert('=IF(ISNA(VLOOKUP($B33,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B33,$A$1:$E$13,COLUMN(C:C),0))')>Attack	:alert('=IF(ISNA(VLOOKUP($B33,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B33,$A$1:$E$13,COLUMN(D:D),0))')>1.6	:alert('=IF(ISNA(VLOOKUP($B33,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B33,$A$1:$E$13,COLUMN(E:E),0))')>90.5
34	3	:alert('=$A$31&A34')>Attack3	:alert('=IF(ISNA(VLOOKUP($B34,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B34,$A$1:$E$13,COLUMN(B:B),0))')>McGrew	:alert('=IF(ISNA(VLOOKUP($B34,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B34,$A$1:$E$13,COLUMN(C:C),0))')>Attack	:alert('=IF(ISNA(VLOOKUP($B34,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B34,$A$1:$E$13,COLUMN(D:D),0))')>1.8	:alert('=IF(ISNA(VLOOKUP($B34,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B34,$A$1:$E$13,COLUMN(E:E),0))')>90.8
35	4	:alert('=$A$31&A35')>Attack4	:alert('=IF(ISNA(VLOOKUP($B35,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B35,$A$1:$E$13,COLUMN(B:B),0))')>Jack	:alert('=IF(ISNA(VLOOKUP($B35,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B35,$A$1:$E$13,COLUMN(C:C),0))')>Attack	:alert('=IF(ISNA(VLOOKUP($B35,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B35,$A$1:$E$13,COLUMN(D:D),0))')>1.8	:alert('=IF(ISNA(VLOOKUP($B35,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B35,$A$1:$E$13,COLUMN(E:E),0))')>90.6
36	5	:alert('=$A$31&A36')>Attack5	:alert('=IF(ISNA(VLOOKUP($B36,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B36,$A$1:$E$13,COLUMN(B:B),0))')>*	:alert('=IF(ISNA(VLOOKUP($B36,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B36,$A$1:$E$13,COLUMN(C:C),0))')>*	:alert('=IF(ISNA(VLOOKUP($B36,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B36,$A$1:$E$13,COLUMN(D:D),0))')>*	:alert('=IF(ISNA(VLOOKUP($B36,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B36,$A$1:$E$13,COLUMN(E:E),0))')>*
37	6	:alert('=$A$31&A37')>Attack6	:alert('=IF(ISNA(VLOOKUP($B37,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B37,$A$1:$E$13,COLUMN(B:B),0))')>*	:alert('=IF(ISNA(VLOOKUP($B37,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B37,$A$1:$E$13,COLUMN(C:C),0))')>*	:alert('=IF(ISNA(VLOOKUP($B37,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B37,$A$1:$E$13,COLUMN(D:D),0))')>*	:alert('=IF(ISNA(VLOOKUP($B37,$A$1:$E$13,1,0))=TRUE,"",VLOOKUP($B37,$A$1:$E$13,COLUMN(E:E),0))')>*
Master

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.20]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo