INDIRECT function - problem when switching from Excel to Google Sheet-CodePudding

Hello everyone and happy new year 2023!

I built an Excel file then, I converted to Google Sheet.

The file contains 2 tabs:

The "Consolidated DB" tab contains the raw data (it will be populated by Make with automatic row additions).
The tab "Parameters Selection" is a dynamic copy of the tab "Consolidated DB" thanks to the following formula: =INDIRECT("'Consolidated DB'!L"&LINE()&"C"&COLUMN();FALSE) that I wrote in cell A2 of the tab "Parameters Selection" and that I pulled on all my rows and columns.

On Excel the formula works perfectly and returns me as desired the values of each cell of the tab "Consolidated DB" in the tab "Parameters Selection". However, once I convert the file into a Google sheet it adapts my formula as follows: =ARRAY_CONSTRAIN(ARRAYFORMULA(INDIRECT("'Consolidated DB'!L"&LINE()&"C"&COLUMN(),FALSE)), 1, 1) and returns me the error below: enter image description here

Do you have any ideas for solutions?

Thank you very much for your help =)

CodePudding user response：

=INDIRECT("'Consolidated DB'!L"&LINE()&"C"&COLUMN();FALSE)

In Google Sheets, you can copy a larger range with one { array expression }, without copying the formula around. Choose Insert > Sheet and put this formula in cell A1 of the new sheet:

={ 'Consolidated DB'!A1:Z }

The range reference in the above formula will adjust dynamically as you insert and delete rows and columns in the source tab. To keep referencing the same range, regardless of tab layout changes, use this instead:

=indirect("Consolidated DB!A1:Z")