Forum Discussion
MS Forms If function ignoring new data
Hello, I have a MS form setup collating information, I need to then reformat how this data comes in so it can be copied directly into another bit of software.
Currently we have sheet 1 as below which is what gets filled in automatically and directly from the form
I have created a new sheet in the same excel file which then pulls the data from sheet 1 and rearranges it into the order I need. This uses the function as below.
=IF(Sheet1!J2="","",Sheet1!J2)
This works fine when initially setup, but when new entries are submitted to the form and new rows of data appear in sheet 1, the If formula on sheet 2 then skips these new data rows.
For example if a new submission to the form has been made, then on sheet 2
cell b5 will say
=IF(Sheet1!J6="","",Sheet1!J6) which is correct
but the cell in the one below will
=IF(Sheet1!J8="","",Sheet1!J8) which has missed J7
Can anyone shed any light on this?
Thank you
7 Replies
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.
Give a try to the following formula-
=MAP(DROP(Form1!J.:.J,1),LAMBDA(x,IF(x="","",x)))Replace Form1 with proper sheet name. If it is Sheet1 then try-
=MAP(DROP(Sheet1!J.:.J,1),LAMBDA(x,IF(x="","",x)))Hi, thanks for above - I tried above but just got a #CALC! error in the cell
Can you please share a sample sheet link?
