Formulas in form responses spreadsheets breaking on new responses

[Davidm54]

Hello all,

I've hit an unexpected snag with a forms response spreadsheet.

Form was created through onedrive, so that the responses go to specific spreadsheet.

I want to be able to create some dynamic info on other sheets. 

In simple terms, even if I literally just do =forms!a2 etc on the second sheet to just mirror a column on a new sheet. As soon as a new responses is added, instead of the cell on second sheet showing the new result, it skips a row. So if the last response was in =forms!a2, when the cell below with =forms!a3 reacts to a new response, instead of just showing it, it changes the formula to =forms1!a4.

I assume it has something to do with responses spreadsheets extending tables on the forms sheet? 

If that's made any sense, help would be appreciated.

(I'll add pics when on other laptop.)

[#2]

@Davidm54 Well, to answer my own question: I must have manually typed formulas for reasons. If you put = in the cell you want, then select the heading of the column you want in a table. It does it right!

[#3]

@Davidm54 do you mind screenshotting or elaborating a bit further? You're tackling exactly what I'm looking for!

[#4]

Sorry, only just saw this. I'll try and nab screens. (I'm three sheets ahead of this now, so I need to find which one I was referring too.)

[#5]

@emilym557 That's what I used. And it works fine. iferror is just to keep things clean. The table you're sending stuff too is probably going to be bigger than the table you're sourcing from, so I just like to have NAs and #VALUEs blank.

Does that make sense? Someone smarter than me will need to explain the use of @for table references as opposed to the Table [#all] methods. Not sure what the functional difference is between them, but they both seem to work. 

[#6]

Hopefully you can see this now.

[#7]

Thanks so much @Davidm54
I found a workaround using Power Automate, will have to test this one out too. Appreciate it!