Indirect Filter on Table in Excel

I have a table as shown in the following image.

I would like to hide the Year column and allow the user to select the year from the value in A1 and filter the results. The value in B2 is a subtotal that shows the min value for Data with the given filter.

Is this even possible?

Filter Table Excluding Filter Column

• If you meant filtering in place, using VBA is the only way.

MS365, Excel 2024

=LET(table,Table1[#All],filter_column_title,"Year",filter_value,A1,
    ah,TAKE(table,1),
    fc,XMATCH(filter_column_title,ah),
    acs,SEQUENCE(,COLUMNS(table)),
    rcs,FILTER(acs,acs<>fc),
    rh,INDEX(ah,rcs),
    ad,DROP(table,1),
    fi,--IFERROR(INDEX(ad,,fc)=filter_value,),
    r,IF(SUM(fi)=0,rh,VSTACK(rh,FILTER(CHOOSECOLS(ad,rcs),fi))),
    r) 

• Adjust the inputs in the first row, e.g., if the data is not in a structured table, in the formula bar select Table1[#All] and select the range (including headers, e.g., A3:D13) instead.
• Replace the bottom-most r with any other variable to see what it holds.

Step-By-Step

• Inputs

  table,Table1[#All],filter_column_title,"Year",filter_value,A1,
  

• All Headers (No Data) [row]

  ah,TAKE(table,1),
  

• Filter Column Index [integer]

  fc,XMATCH(filter_column_title,ah),
  

• All Column Indices [row]

  acs,SEQUENCE(,COLUMNS(table)),
  

• Resulting Column Indices [row]

  rcs,FILTER(acs,acs<>fc),
  

• Resulting Headers (No Filter Column) [row]

  rh,INDEX(ah,rcs),
  

• All Data (No Headers) [array]

  ad,DROP(table,1),
  

• Filter (1s and 0s) [column]

  fi,--IFERROR(INDEX(ad,,fc)=filter_value,),
  

• Result (only headers if no match) [row]or[array]

  r,IF(SUM(fi)=0,rh,VSTACK(rh,FILTER(CHOOSECOLS(ad,rcs),fi))),