Auto populating rows from another sheet conditional to a specific cell value

Question

Hello i am trying to autopopulate rows from another sheet if a specific value if found in a specific cell. So far, i managed to do it manually by adding this line in Sheet #2 for each cell.

=IF(OR('Le 2250'!$C48="Nouveau locataire",'Le 2250'!$C48="Décès", 'Le 2250'!$C48="Retention"), 'Le 2250'!$B48,"")

I am trying to create a vba script that will generate each column in Sheet#2, and that will dynamically update regarding if i add a row in Sheet 1 or delete it.

Sheet1 is:

Sheet2 is:

Your help is appreciated

Answer 1

What your looking for is the INDIRECT function

As an example:

=CELL("contents",INDIRECT("Sheet1!B5"))

This always pick up the value in Sheet1 Cell B5, regardless of changes in Sheet1.

For more information: Excel INDIRECT Function

---

Edit: To directly answer the question with INDIRECT.

=IF(OR(INDIRECT("'Le 2250'!C48")="Nouveau locataire",INDIRECT("'Le 2250'!C48")="Décès"),CELL("contents",INDIRECT("'Le 2250'!B48")),"")