Excel: Reference Range from other worksheet

Vote count:

0

How can I reference a range from sheet1 in sheet2 while all changes especially adding or removing rows are accordingly reflected on sheet2?

For example I have a table on sheet1 from B20 to D24. (4 columns, 5 rows). This are should be referenced on sheet2 from A2 to C6. If user inserts row it should be added as well to the referenced sheet. If one is removed, same applies.

if I do =Sheet1!B20 and so forth new rows are ignored and I get a gap. Say a new row is added after row 20 only references from row 20 and 22 (previously 21) are shown on sheet2. If I remove a row I get a #ref error. With an array formula additions are ignored and deletions get a #na error.

How can I solve this especially without vba?

asked 51 secs ago

beginner_

1,301

Excel: Reference Range from other worksheet