Google Sheets – Fix Value of Row or Column from Another Sheet

This sounds like a job for ARRAYFORMULA. Enter this in your Sheet 2 cell, where you want the values copied to:

=ARRAYFORMULA('Sheet 1'!A2:B)

When you enter new values in Sheet 1, the new values will automatically be present in Sheet 2. Only the A2:B range is included.

Feel free to copy this spreadsheet that I set up to test, and check the documentation for ARRAYFORMULA.

You can use index() and match(), along with absolute references for this. I don't know the details of the first row and column in either sheet so I assume that they will be populated by you. You can then put a formula like the following starting from B2 on sheet #2:

=iferror( index(
    'Sheet 1'!$A:$N,
    match($A2, 'Sheet 1'!$A:$A, 0),
    match(B$1, 'Sheet 1'!$1:$1, 0)
))

This formula simply pull the cell from 'Sheet 1'!$A:$N with matching first row and column. You will need to update the range accordingly if your data on sheet 1 goes beyond column N.

In the row matching function, you want to make the column references absolute so that you will always be comparing the first column on each row when you drag the formula across horizontally. Likewise, in the column matching function, you want to make the row references absolute so that you will always be comparing the first row on each column when you drag the formula down.