I have created a form in MS Excel, which automatically populates a file in my OneDrive Business. So for Example when ever someone will fills out the form it will populate a row in an excel sheet (this is a feature of Microsoft). The responses from form are saved in a sheet called Form as shown below:
Now what I want is that I want to populate some information in Sheet1 from the workbook named Form. So for example I am using the formula:
=IF(ISBLANK(Form!D2),"",Form!D2)
in Cell D2 of Sheet1 as shown below:
And I am using the same formula on the cells underneath, so that whenever a form is filled this space gets automatically populated... In Cell D2 of Sheet1 the formula is =IF(ISBLANK(Form!D2),"",Form!D2) in the next cell it is =IF(ISBLANK(Form!D3),"",Form!D3) and so on.
My problem is that whenever a form is filled the formula in the next empty row of Sheet1 automatically changes to one increased number. For example in row 7 of sheet1 instead of =IF(ISBLANK(Form!D7),"",Form!D7) it will automatically become =IF(ISBLANK(Form!D8),"",Form!D8) when the form is filled hence not populating the row in Sheet1 because of the mismatch.
Any ideas why is it happening and how to resolve it? Any help would be great.
Thank you.