Excel code redirecting to first blank cell

[flipflopsonfifthavenue]

I am using Microsoft® Excel® for Microsoft 365 MSO (Version 2109 Build 16.0.14430.20154) 32-bit. I have data in an Excel table which is being automatically populated line by line as users complete a questionnaire in Microsoft Forms. Then I have a second tab in my spreadsheet where I am converting the text-based responses to the form into numerical values. In this second tab, I have a formula which should "pull" the responses from the other tab as they're populated. However, the formula in this tab keeps redirecting to the first blank row in the first tab, where the responses are auto-populated, instead of pulling through the "new" response from the first tab.

E.g. customer #15 completes questionnaire populating 'Raw data'!A15

Cell A15 in tab Numerical Responses contains the formula "='Raw data'!A15", except that instead of populating this row, the formula changes to refer to 'Raw data'!A16, aka the first blank row. Why is this happening and how can I fix it?

[#2 · Accepted solution]

@flipflopsonfifthavenue 

I guess formula reference on the next after the bottom of structured cell row. Since table is auto expandable Excel interpret such reference not as on cell address in, but as relative reference on next after the table cell. With expansion of cell it moves such reference.

In general with structured tables it's always better to use index for the reference on the cell, like

=IFERROR( INDEX(Table1[A], 10), 0)

[#3]

Aha! Yes it's the table changing. Thanks, worked it out now with table references, I think!