Hi All

I have a table on Worksheet A, unimaginatively named “Table1”, with fields including “CODE”.

On Worksheet B, in cell C1, I have this formula:

=FILTER(Table1,(Table1[CODE]=”T1″) + (Table1[CODE]=”P1″))

This works fine i.e. it shows a new table with all the rows where CODE = “T1” or “P1”.  Lovely.

Also on Worksheet B, in cell A1, I have the following string:

(Table1[CODE]=”T1″) + (Table1[CODE]=”P1″)

I have created this string using logic based on the data in Table1.  On another day, the string literals might change and there may be more OR-ed elements, perhaps:

(Table1[CODE]=”ENG002″) + (Table1[CODE]=”BBBB”)  + (Table1[CODE]=”Z YW”)

Essentially this string is volatile and I don’t want to hard-code it as in the first example.

How can I use successfully the string in cell A1 as the ‘include’ parameter to the FILTER function? 

I tried:

=FILTER(Table1,A1)

but this gives #VALUE!

I thought INDIRECT might work but:

=FILTER(Table1,INDIRECT(A1))

gives a #REF!

I think I am missing something obvious but can’t see it.  Can you help, at all?  Thanks VM.

Peter

​Hi All I have a table on Worksheet A, unimaginatively named “Table1”, with fields including “CODE”.On Worksheet B, in cell C1, I have this formula:=FILTER(Table1,(Table1[CODE]=”T1″) + (Table1[CODE]=”P1″))This works fine i.e. it shows a new table with all the rows where CODE = “T1” or “P1″.  Lovely. Also on Worksheet B, in cell A1, I have the following string:(Table1[CODE]=”T1″) + (Table1[CODE]=”P1″) I have created this string using logic based on the data in Table1.  On another day, the string literals might change and there may be more OR-ed elements, perhaps:(Table1[CODE]=”ENG002″) + (Table1[CODE]=”BBBB”)  + (Table1[CODE]=”Z YW”)Essentially this string is volatile and I don’t want to hard-code it as in the first example. How can I use successfully the string in cell A1 as the ‘include’ parameter to the FILTER function?  I tried:=FILTER(Table1,A1)but this gives #VALUE!I thought INDIRECT might work but:=FILTER(Table1,INDIRECT(A1))gives a #REF! I think I am missing something obvious but can’t see it.  Can you help, at all?  Thanks VM.Peter  Read More

​