I have 2 sheets in Google Sheets that are linked.
Sheet1 is the 'root source'.(Sheet1 is a Google Form sheet that automatically updates with new responses).
Sheet2 uses the formula "=Sheet1!C2" to transfer values from Sheet1 to a specific column (in Sheet2). The formula in Sheet2 is expanded/copied so that "=Sheet1!C2" is followed by "=Sheet1!C3", "=Sheet1!C4", and so on.
The problem: Every time a new entry in Sheet1 is created (i.e. a new response is created), Sheet2 does not get a new entry (but it should since I have defined it using the mentioned formulas). When inspecting the formula, I see that cell in the formula (like C3, C4, ...) gets shifted upwards by 1. Therefore, the cell (still in Sheet2) that was defined with the formula "=Sheet1!C3" is automatically changed to "=Sheet1!C4".
How can I prevent this automatic upwards shifting of the formula when a new response/entry is created in Sheet1 (so that it automatically arrives in Sheet2 as well)?
Thank you!
