Filter Value from table based on selection

[fayez_MrExcel]

I have Product Tables in each sheet with the name as per below.
Product_1
Product_2
Product_3

Each Table has the same format that includes the Product ID located in Column A.

How can filter the Product ID from the correct Product table if I interactively select the Product Table? For example, if I select Product_3, Product A001, Product A002, and Product A003 will be filtered and if I select Product_2 table, Product A, Product B, and Product C will be filtered.

Sheet 1 (Table Name: Product_1)	Sheet 2 (Table Name: Product_2)	Sheet 3 (Table Name: Product_3)
Product ID	Product ID	Product ID	Product Table Name:​	Product_3
Product 001	Product A	Product A001	Expected Filtered lists is: ​	Product A001
Product 002	Product B	Product A002		Product A002
Product 003	Product C	Product A003		Product A003

 

[Cubist]

Try:

=INDIRECT("A1")

 

[fayez_MrExcel]

Try:

=INDIRECT("A1")

Thank you.. that works... I used the formula as: =FILTER(INDIRECT(A1&"[Product ID]"),INDIRECT(A1&"[Product ID]")<>"").. Where A1 is the name of the table

 

[Cubist]

If you want to filter out blanks use TOCOL() instead, the syntax is a bit cleaner.

=TOCOL(INDIRECT(A1&"[Product ID]"),1)