Filter function help with structured references

%3CLINGO-SUB%20id%3D%22lingo-sub-3124045%22%20slang%3D%22en-US%22%3EFilter%20function%20help%20with%20structured%20references%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3124045%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3EI%E2%80%99m%20trying%20to%20use%20filter%20function%20to%20filter%20a%20table%20from%20another%20worksheet%20so%20that%20when%20data%20gets%20copied%20into%20the%20table%20the%20other%20worksheet%20updates.%20It%20works%20fine%20with%20cell%20references%20but%20I%20want%20to%20be%20able%20to%20add%20new%20data%20to%20the%20table%20and%20have%20my%20filtered%20worksheets%20update.%20As%20soon%20as%20I%20use%20a%20structured%20reference%20or%20used%20named%20ranges%20or%20just%20select%20the%20whole%20column%20then%20I%20get%20a%20value%20error.%20What%20am%20I%20doing%20wrong%20please%3F%3CBR%20%2F%3E%3DFILTER(%E2%80%98Data%E2%80%99!A2%3AT11%2C%E2%80%99Data%E2%80%99!T2%3AT11%3DA1)%20is%20what%20works.%3CBR%20%2F%3E%3CBR%20%2F%3EAlternatively%2C%20is%20there%20a%20better%20way%20to%20do%20this%3F%3CBR%20%2F%3EThanks%20for%20your%20help!%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3124045%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3124516%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20function%20help%20with%20structured%20references%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3124516%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1298888%22%20target%3D%22_blank%22%3E%40Katie_Be%3C%2FA%3E%26nbsp%3BDifficult%20to%20answer%20if%20you%20don't%20show%20the%20formula%20that%20returns%20the%20value%20error%20or%20the%20table%20the%20formula%20references.%20Structured%20references%20are%20a%20bit%20tricky%20to%20write%20from%20scratch%2C%20but%20if%20you%20create%20the%20formula%20by%20selecting%20the%20ranges%20inside%20the%20structure%20table%2C%20Excel%20will%20automatically%20include%20the%20right%20syntax.%20With%20a%20table%20called%20%22Table1%22%20such%20a%20formula%20could%20look%20like%20this%208just%20as%20an%20example)%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202022-02-07%20at%2008.20.53.png%22%20style%3D%22width%3A%20490px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345746i360E2234BEF01038%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-02-07%20at%2008.20.53.png%22%20alt%3D%22Screenshot%202022-02-07%20at%2008.20.53.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EOnce%20you%20have%20a%20structured%20table%2C%20you%20no%20longer%20need%20to%20include%20the%20sheet%20name%20when%20you%20enter%20the%20formula%20in%20another%20sheet.%20In.%20other%20words%2C%20it%20doesn't%20matter%20where%20the%20table%20sits%20in%20the%20workbook.%20As%20long%20as%20you%20spell%20the%20table%20and%20column%20names%20correctly%2C%20Excel%20will%20find%20the%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor
Hi,
I’m trying to use filter function to filter a table from another worksheet so that when data gets copied into the table the other worksheet updates. It works fine with cell references but I want to be able to add new data to the table and have my filtered worksheets update. As soon as I use a structured reference or used named ranges or just select the whole column then I get a value error. What am I doing wrong please?
=FILTER(‘Data’!A2:T11,’Data’!T2:T11=A1) is what works.

Alternatively, is there a better way to do this?
Thanks for your help!
1 Reply

@Katie_Be Difficult to answer if you don't show the formula that returns the value error or the table the formula references. Structured references are a bit tricky to write from scratch, but if you create the formula by selecting the ranges inside the structure table, Excel will automatically include the right syntax. With a table called "Table1" such a formula could look like this 8just as an example):

Screenshot 2022-02-07 at 08.20.53.png

Once you have a structured table, you no longer need to include the sheet name when you enter the formula in another sheet. In. other words, it doesn't matter where the table sits in the workbook. As long as you spell the table and column names correctly, Excel will find the data.