Forum Discussion
Using formulas in a table
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?
4 Replies
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).
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.
- 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.
