I have attached a basic demo of my question, but can't show all that's going as I'm using MS forms to gather data.
In the real spreadsheet the below is being sourced from a Power queried copy of an MS form spreadsheet. (Done this way to keep the original data intact, but also because I have a static spreadsheet that I want to link.)
I have 2 problems, at least.
1. Obviously, as more responses come in, the table will grow, and every time I refresh the data, the count/total column at the bottom resets/data disappears. The totals column at the bottom is easy enough to add in the sheet, and it gives you the drop downs, but I'm wondering if there's a way to add a totals row in PQ itself. The count option in there creates a new list/table.
2. Because the rows will expand, it makes it hard to reference the totals column to use in the master list.
There's other columns etc, but fundamentally, I want the total count of sessions and Did Not Attends to update automatically in the static list. (Names etc in that list are manually entered, it's the source of info for most other columns.
I've toyed with setting up separate pivot tables to try and keep the totals static, and I know I can do a static ref for a particular column, but it's doing that while also separating the totals by client name and allowing for new clients to appear that's killing me.
Any thoughts appreciated. (I'm not married to any particular solution, I like forms generally as a workflow, but if forms makes things harder they can go.)
I want the solution to not involve any manual work from an end user, if possible.
Thanks in advance if you have any ideas.
