0

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

Xabier
  • 7,587
  • 1
  • 8
  • 20
Lou
  • 31
  • 5

1 Answers1

0

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")),"")
JosephC
  • 917
  • 4
  • 12
  • Can I integrate the if function in the cell function? I am not quiet understanding this cell(contents, function) – Lou Jul 18 '18 at 14:19
  • works like a charm. is there a way to integrate this function in vba so it populates all the rows? – Lou Jul 18 '18 at 14:29
  • Cell is a function that can return information on the cell. The way it is used in this case was to return the contents of the cell. For more information: [Excel Cell Function](https://www.excelfunctions.net/Excel-Cell-Function.html) – JosephC Jul 18 '18 at 14:41