How to make a dynamic import command from one sheet to another in google sheets

Question

I want to build a tool that lets me insert a spreadsheet in a given format into sheet1. Then it takes some data and inserts it into a specified location in sheet2.

I am using functions in the following format at the moments:

=Sheet1!AI2

For every column, I need the data from all rows i.e sheet1 A1 to sheet2 B2, Sheets A2 to sheet B3 ...

There are hundreds of rows and I don't want to copy-paste it and manually change the row number.

Is there a way to do it like this:

=Sheet1!AI(rownumber-1)

That way it would work for all rows.

Thanks for taking the time and reading this!

Answer 1

Try putting this in Sheet2!B2

=ARRAYFORMULA(Sheet1!AI:AI)

or this (it will give you the same result):

=INDEX(Sheet1!AI:AI)

Be sure that Sheet2 has at leas one more row than Sheet1. Google Sheets will automatically add 500 new rows, but if the numbers of rows in those sheets are codependent than rows will be added in a loop up until the limit.