Dynamic Filter

[RAMU]

Hi,
I have an excel file where Sheet1 & Sheet2 carrying Data.
In Sheet one is a table (Table1) mentioned below:

item Code	Closing qty
apple	45​
ginger	25​
guava	65​
mango	30​
potato	50​

In sheet2 a bunch of Data are in A1:C9

item Code	warehouse	qty
guava	1​	20​
mango	2​	30​
potato	1​	50​
ginger	2​	25​
guava	1​	25​
apple	3​	20​
apple	1​	25​
guava	3​	20​

I need a dynamic filter formula in F1 (Sheet2), when I shall filter the Table1 (Sheet1) Item Code Column, as an example if I filter it as guava, in the F1 of Sheet 2 will give me result like this:

item Code	warehouse	qty
guava	1​	45​
guava	3​	20​

Pls help
Thanks in advance
Regards
RAMU

 

[hagia_sofia]

Hello, what version of Excel are you using?

 

[RAMU]

Hello, what version of Excel are you using?

MS office 365

 

[Fluff]

MS office 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Skyybot]

Wouldn't a PivotTable of Sheet2 data work?

 

[hagia_sofia]

The following might be an option if I understand correctly what you are after:

=LET(
u,UNIQUE(A2:B9),
a,UNIQUE(A2:A9&B2:B9),
b,DROP(REDUCE("",a,LAMBDA(x,y,VSTACK(x,SUM(FILTER(C2:C9,A2:A9&B2:B9=y))))),1),
c,HSTACK(u,b),
IFERROR(FILTER(c,CHOOSECOLS(c,1)=FILTER(Sheet1!A2:A6,BYROW(Sheet1!A2:A6,LAMBDA(x,SUBTOTAL(3,x))))),0))

But it is rather complicated as @Skyybot rightly pointed out - i.e. it might be easier and more user-friendly with pivot table or at least with drop-down list.

 

[RAMU]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Done Sir, thanks for your advice

 

[RAMU]

The following might be an option if I understand correctly what you are after:

=LET(
u,UNIQUE(A2:B9),
a,UNIQUE(A2:A9&B2:B9),
b,DROP(REDUCE("",a,LAMBDA(x,y,VSTACK(x,SUM(FILTER(C2:C9,A2:A9&B2:B9=y))))),1),
c,HSTACK(u,b),
IFERROR(FILTER(c,CHOOSECOLS(c,1)=FILTER(Sheet1!A2:A6,BYROW(Sheet1!A2:A6,LAMBDA(x,SUBTOTAL(3,x))))),0))

But it is rather complicated as @Skyybot rightly pointed out - i.e. it might be easier and more user-friendly with pivot table or at least with drop-down list

Pivot Table also accepted but how can I link the filter in between the Table of sheet1 & pivot table of Sheet2 ? Kindly help

 

[RAMU]

Wouldn't a PivotTable of Sheet2 data work?

Hi,
Yes no issue using pivot table but is it possible to link the filter of Table in sheet1 & Pivot in sheet2 ? Also suppose instead of filtering the item code in the table if I only select the Item Code in Table, Can it be possible to auto filter the same item code in Pivot & get the data ?

 

[RAMU]

Pivot Table also accepted but how can I link the filter in between the Table of sheet1 & pivot table of Sheet2 ? Kindly help

The following might be an option if I understand correctly what you are after:

=LET(
u,UNIQUE(A2:B9),
a,UNIQUE(A2:A9&B2:B9),
b,DROP(REDUCE("",a,LAMBDA(x,y,VSTACK(x,SUM(FILTER(C2:C9,A2:A9&B2:B9=y))))),1),
c,HSTACK(u,b),
IFERROR(FILTER(c,CHOOSECOLS(c,1)=FILTER(Sheet1!A2:A6,BYROW(Sheet1!A2:A6,LAMBDA(x,SUBTOTAL(3,x))))),0))

But it is rather complicated as @Skyybot rightly pointed out - i.e. it might be easier and more user-friendly with pivot table or at least with drop-down list.

The formula showing 0