Forum Discussion
MS Forms If function ignoring new data
Forms generates structured table within sheet. As a comment, it's better to work with structured table references rather than sheet references.
If we need new table to play with, I'd duplicate it in new sheet with Power Query. Also here you may do some transformations of the source table if needed. To work with it it's only to Refresh before.
If the goal is only to sort source data in some order I'd do that directly in source table. You may also added to it some helper columns if needed for more flexibility.
When new response arrives Forms adds it at the bottom of source table, sort you it or not and add extra column(s) or not. That's only to resort each time with new data.
I have had a look at Power query but don't understand it. If i go to Get Data..From Table/Range, and then select the source data I get an error that tables cant overlap.
Can I ask regarding editing the source table. For example, if i moved some of the columns into different positions, or inserted new columns where we added other data, would the MS form still map to these moves?
With Power Query - stay on source table, Data -> From table/Range -> Transform -> do something if necessary -> File -> Close & Load
-> select destinattion
Source table editing. Nope, you have to keep columns as they are, without renaming them or changing position. However, you may add new columns.
I'm not 100% sure you can't change columns position, Microsoft changed sync process dramatically during last year. Perhaps it's enough to keep names. Better to test, it won't take a lot of time.
