New Google Tables in Formulas

[Michael Timpano]

Hello,

I am curious why the formula works with I reference a named range 'Data' the formula works but when I reference the table name Registrations[#ALL] it does not?

The named range is in the sheet where the table exists (table is titled Registrations). Sheet name is Processed Responses. The named range 'Data' is in fact referencing  'Processed Responses'!A2:AW1144 which is the entire Registrations table excluding the header row.

This formula works, but reference the entire table does not?

=IF(
  OR(LEN(A2), LEN(A7), LEN(A10)),
  IFERROR(
    FILTER(
      Data,
      (
        (LEN(A2) = 0) +
        (Registrations[SJO/JOA/PET Winter Dual Credit Options ] = A2) +
        (Registrations[STH Winter Dual Credit Options ] = A2) +
        (Registrations[STS & HTR Winter Dual Credit Options ] = A2) +
        (Registrations[PFO Winter Dual Credit Options ] = A2) +
        (Registrations[OLB Winter Dual Credit Options ] = A2) +
        (Registrations[DOM Winter Dual Credit Options ] = A2) +
        (Registrations[ ADC  Winter Dual Credit Options]= A2)
      ) > 0,
      (LEN(A7) = 0) + (Registrations[Status] = A7),
      (LEN(A10) = 0) + (Registrations[School Attending:] = A10)
    ),
    "No current registrations"
  ),
  "No input detected"
)

formula that does not work using table reference #ALL

=IF(
  OR(LEN(A2), LEN(A7), LEN(A10)),
  IFERROR(
    FILTER(
      Registrations[#ALL],
      (
        (LEN(A2) = 0) +
        (Registrations[SJO/JOA/PET Winter Dual Credit Options ] = A2) +
        (Registrations[STH Winter Dual Credit Options ] = A2) +
        (Registrations[STS & HTR Winter Dual Credit Options ] = A2) +
        (Registrations[PFO Winter Dual Credit Options ] = A2) +
        (Registrations[OLB Winter Dual Credit Options ] = A2) +
        (Registrations[DOM Winter Dual Credit Options ] = A2) +
        (Registrations[ ADC  Winter Dual Credit Options]= A2)
      ) > 0,
      (LEN(A7) = 0) + (Registrations[Status] = A7),
      (LEN(A10) = 0) + (Registrations[School Attending:] = A10)
    ),
    "No current registrations"
  ),
  "No input detected"
)

Thank you in advance for this concern. Take care.

[Rani Urbis]

I suggest that you modify your table reference formula to exclude headers and totals. 

Instead of #ALL, use #Data to refer only to the table's data without headers and totals. The #ALL does not seem to work with FILTER function.  Also, if your Named Range formula worked, it is because Data refers to the cell range A2:AW1144, which excludes the header row.

Hope this helps.

[Michael Timpano]

Thanks for your response. I did use [#DATA] and realize the google sheets removed it automatically and left just the table name without any other parameters.

This seems to work;
 FILTER(
      Registrations,
      (

[Rani Urbis]

Glad you had it working now.