Why can't I use vstack on a table?

Question

Hello,

Total novice here. This Excel sheet is a Form Response with scoring and comments.

I'm trying to pull data from Sheet1 into another Sheet called "With Formula" where I've already created the formulas for averages. The "With Formula" sheet has been formatted into a table for when I pull the data into Power Automate. I tried Vstack to pull Sheet1 data into "With Formula" sheet but there is an error: Spill range in table. We can't spill within a table. Does this mean Vstack doesn't work in a table? If so, what's the best way for me to pull the data?

Thank you!

Answer 1

That still doesn't show me the data as it comes in from MS Forms. And I don't know much about working with Forms so I can't help you with that. But I assume it creates an Excel file on OneDrive. With Power Query (i.e. that is not the same as Power Automate) you can connect to that file, load it to Excel, add your formulas and then use Mail Merge to get it into Word, if I understood that other thread correctly.

Answer 2

VSTACK, like many of the newer functions spill dynamic arrays (i.e. output over a range of cells in one go). You simply can't use these functions inside a structured table as thes already have some dynamic features in them. So, that's by design.

"what's the best way for me to pull the data?"

Why can't you work with the data as it is. Transform it into a table and add columns BE:BK. When you get new data with 10 more rows, just drop it into that table in A2. It should fill A2 to BD18 and the formulas in BE:BK will automatically be copied down into the larger table.

Answer 3

Thank you for clarifying vstack.

I created a new sheet because I'm pulling from a live response sheet. This is a form responses sheet where the Form will be live for as long as we're using this tool. The tool has scoring and comments that I wish to capture and auto populate in a Word template. I've checked with the Power Automate community and it seems I need to convert the data into a table before proceeding with the rest of the flows. Is there another solution to pulling the live data into the "With Formula" sheet?

Answer 4

Perhaps Power Query can be helpful here. But I can't tell without seeing your data and without knowing what exactly you want to do with it.

Answer 5

I asked my original question here: https://community.powerplatform.com/forums/thread/details/?threadid=6c55c6d3-2d85-ef11-ac21-6045bdef2451

I hope this explains it better.