How to properly reference cells to avoid REF error when modify them

Question

I have this spreadsheet: https://docs.google.com/spreadsheets/d/1nQzpJ1cOT8kdo-b_vFFoSIHyLMhqd__Cv4SipncVTnA/edit?usp=sharing

In Sheet 2:

   A       B       C       D 
col1    col2    col3    col4
   1       2       3       4
   2       4       6       8
   1       3       5       7

Then I want to copy all these numbers in Sheet1, so in Sheet 1 I have this formula in the cell A3:

A3: =IFERROR(INDIRECT("'"&B2&"'!A2:O"),"")

Where in B2 of Sheet1 I have the string "Sheet2".

Everything works fine. However, If I try to modify one of the cells which is referenced in Sheet1 (for example B3) I get

#REF error

Why is this and How can I avoid it?

player0 player0 · Accepted Answer · 2019-02-26T14:02:00

it's because the range you selected to import from other sheet is A2:O which includes empty cells. to resolve it you can use dynamic ARRAY_CONSTRAIN and skip the empty cells with COUNTA like:

=ARRAY_CONSTRAIN(IFERROR(INDIRECT("'"&B2&"'!A2:O"), ), 
 COUNTA(Sheet2!A2:A), COUNTA(Sheet2!A2:2))

to modify B3 on Sheet1 you will need to go in Sheet2 and modify it there because those data are only "visually" imported from another sheet. eg. when you modify cell B3 then cell B3 is no longer empty and the stuff you want to import has no space to be enrolled (it can be enrolled only on empty cells)

How to properly reference cells to avoid REF error when modify them

1 Answers