formulas reading from wrong cell when reading from a worksheet updated by MS forms

Question

I have created a form using MS forms and saved the excel sheet that collates all the data in SharePoint.

I have created a 2nd worksheet to read from the one that forms auto populates so we can add some more columns for extra data.

The problem I am having is when MS forms auto populates a row, my formulas in the 2nd worksheet somehow automatically increment by 1 number. So the worksheet I created reads the row below the one that has just been auto populated and therefore does not copy over the data.

I've tried formulas with and without $ signs in the cell references and it makes no difference.

I've tried locking the cells, that doesn't work.

Answer 1

Could you share us a test file without sensitive information to reproduce your issue? Have you tried mark the range as table and use table formula?

For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.