Filter with Multiple Criteria not working

[gj80]

Could I please ask for some help with regards to Filters with multiple criteria.

I am working across two spreadsheets.

The spreadsheet where I’m asking the data to return is using a drop-down list for B2 and B3. B3 is using the indirect formula to create the drop down.

The spreadsheet that holds the data is in a table and the cells contain text not formulas.

I can get a single filter to work using this formula:

=FILTER('Master past Sales Data.xlsx'!SKU[#All],'Master past Sales Data.xlsx'!SKU[[#All],[Category2]]=B2)

This works

But when I add the multiple criteria it return #N/A

=FILTER('Master past Sales Data.xlsx'!SKU[#All],('Master past Sales Data.xlsx'!SKU[[#All],[Category2]]=B2)*('Master past Sales Data.xlsx'!SKU[[#All],[Family]]=B3),"")

This Doesn’t work

Any Advice on what I am doing wrong? Or is there an alternative I can use to filter?

Many Thanks in advance for your help

Gemma

 

[Fluff]

What version of Excel are you using?

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’)

This Doesn’t work

In what way?

 

[gj80]

Thank you, I have updated my details - We have Excel 365 and i have a windows PC

 

[RoryA]

Are there any #N/A values in the Family column (or in B3)?

 

[Fluff]

I have updated my details

Thanks for that. Can you also answer my other question?

 

[gj80]

Are there any #N/A values in the Family column (or in B3)?

Yes i did - Thank you so much it is now working.

 

[gj80]

Are there any #N/A values in the Family column (or in B3)?

Hi, i have another issue i was wonder if you could help me with? When i close the workbook with all the data the filter does work. Is there a way i can adapt the formula to work when the other workbook is closed?
Thanks
Gemma

 

[Fluff]

You would need to use range references, rather than structured references.