excel reference column A in sheet2 from column A in sheet1-CodePudding

Let's say I have a columnA in sheet1 like:

I wanna create a column in sheet2 that exactly reference to this column in sheet1. But:

when I insert number "10" in ColumnA sheet1, ColumnA in Sheet2 shall automatically add this "10" as well.
when I delete number "5" in ColumnA sheet1, ColumnA in Sheet2 shall automatically delete this "5" as well.

So basically I want the range of column to be reference/dynamic. If we use {vlookup} or simple reference "='Sheet1!A5", that will give #REF! or #N/A, but the error line is still there.

Does anyone know whether this is achievable in excel?

CodePudding user response：

Use INDEX:

=IF(INDEX(Sheet1!A:A,ROW())&""="","",INDEX(Sheet1!A:A,ROW()))

Copy this down as far as you think will cover the most that will ever be on sheet1

If one has Office 365 then simply put:

=FILTER(Sheet1!A:A,Sheet1!A:A<>"")

in the first cell and it will spill the results and change as the range changes.

CodePudding user response：

You can use formula Indirect() to pull this off.

Indirect() allows you to refer to a cell by its name, instead of referring to it directly. This way even if you delete a row, the name itself still exists so Indirect() is happy.

In Sheet2, cell A1:

=Indirect("Sheet1!A" & Row())

Copy and paste down and it should behave the way you are asking.

I'm using Row() as well so you can copy/paste this formula instead of having to hard code the row number in the cell name. You could just as well do =Indirect("Sheet1!A1") but then you can't copy the formula down without changing the formula manually in every row, and who has time for that?

You noted in the comment that you get back 0 when the cell referrenced in indirect is empty. In that case just wrap this formula an in if() to suppress 0 values:

=If(Indirect("Sheet1!A" & Row()) = 0, "", Indirect("Sheet1!A" & Row()))

CodePudding user response：

(Not an answer, but too large to fit into a comment)

I advise you to check the Worksheet_Change event for that, that macro might be launched whenever something is changed to a sheet, but beware:

Imagine you have following situation:

Sheet A:

Sheet B:

You say that removing 3 in sheet A also should cause removing 3 in sheet B and vice versa, but what when you have two columns:

Sheet 1:

1    Brussels
2    Ghent
3    Kortrijk
4    Brugge
5    Wervik

and sheet 2:

1    Berlin
2    Köln
3    Frankfurt
4    Stuttgart
5    Reichenbach

Which of the four possibilities should it become?

Possibility 1: (remove cell in sheet 1 and cell in sheet 2)

Sheet 1

1    Brussels
2    Ghent
4    Kortrijk
5    Brugge
     Wervik

Sheet 2:

1    Berlin
2    Köln
4    Frankfurt
5    Stuttgart
     Reichenbach

Possibility 2: remove entire row in sheet 1 and single cell in sheet 2

Sheet 1

1    Brussels
2    Ghent
4    Brugge
5    Wervik

Sheet 2:

1    Berlin
2    Köln
4    Frankfurt
5    Stuttgart
     Reichenbach

Possibility 3: remove single cell in sheet 1 and entire row in sheet 2)

Sheet 1

1    Brussels
2    Ghent
4    Kortrijk
5    Brugge
     Wervik

Sheet 2:

1    Berlin
2    Köln
4    Stuttgart
5    Reichenbach

Possibility 4: remove entire row in sheet 1 and entire row in sheet 2)

Sheet 1

1    Brussels
2    Ghent
4    Brugge
5    Wervik

Sheet 2:

1    Berlin
2    Köln
4    Stuttgart
5    Reichenbach