Search code examples
vbaexcelauto-populateauto-populating

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

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:

enter image description here

Sheet2 is:

enter image description here

Your help is appreciated

Solution

  • 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")),"")