I'm trying to copy certain cells of data received via an Excel Survey to another sheet. Trouble is, when new data is received, the row numbers in the formulas of the sheet to which I'm copying change by advancing up one.
That is, I'm trying to automatically copy data from successive rows of the sheet "Survey1" to another sheet "Sheet2" as survey results come in. I use the formula "=CONCATENATE(Survey1!I2, Survey1!J2, Survey1!K2, Survey1!L2)" in Row 2 of Sheet 2 and copy it down Column A. When new data is received, say in Survey1, Row 3, the formula in Sheet2, A3 changes to "=CONCATENATE(Survey1!I4, Survey1!J4, Survey1!K4, Survey1!L4)" and the data is not copied.
Also, I need to accomplish this without macros.
Can anyone help?
Automatically Copy New Survey Data to Another Sheet
As you are concatenating from row 2 in Sheet 1, in Sheet 2, start your formula in row 2 and then drag it down. You will then see that the row numbers in the formula will match on both sheets. Reserve row 1 for any headings.
This should solve the incorrect increment for you.