Using formulas in a table

[Gigi_PHD]

Hi there

Is anyone able to assist me with the below issue:

We currently have an excel response sheet from a microsoft form. The first tab collects all the responses in their necessary category 

Based on the request, there a numerous different questions to those requests. So we created a new tab for each request and used formulas to automatically populate the data based on the request type in their relevant tabs. Therefore, it only inputs the relevant questions

However, when we try format this as a table, we get the below error:

Upon doing some research, we have found ways to call data from a table but even then the resulting data is not in a table format. 

Please help! how do you format you data as a table when there are formulas in the data?

[#2]

@Gigi_PHD 

It sounds like your formulas may be spilling?

For example, if I create a 5x5 matrix with SEQUENCE, I can produce the same error when attempting to table it:

The issue Excel has is tables, for all their conveniences, are scalar based (1 formula goes in 1 cell producing 1 result).

[#3]

@Patrick2788 

Thank you for the response. Yes, sometimes we do get the spill error.

We tried creating a table out of the cells before inputting the formulas and that's when we get the spill error or it just results in "0".

Is there any solution for getting your resulting data into a table format without creating static text because we need these tables to update automatically.

[#4]

You'd have to choose between using dynamic array formulas that spill or using a table with lookup formulas that do not spill. A function like FILTER, for example, cannot return a spill within a table.

[#5]

Thank Patrick :)

We were originally using the filter function but that doesn't work in table format. We can call data from a table but cannot put the resulting data in a table