Search code examples
excelexcel-2013

How to automatically populate column values into another sheet in Excel?

I have certain columns in sheet one of an excel file that looks like this.

enter image description here

The player No and the Player has been manually created while the Sold to column has been selected using data validation (list).

Right now the sheet two of the excel file looks like this.

enter image description here

Now the question is how do I automatically populate the values of Player No and Player into the respective team(s)?

Expected output is as follows :-

enter image description here

I am currently using Excel version 2013. Can you suggest whether I should use any formula or Macros to achieve this ? Many thanks in advance.

Solution

  • You need to filter data based on condition. There are many way to do that. One of approch is as following.

    As per below screenshot use below formula

    =IFERROR(INDEX($A$2:$A$7,AGGREGATE(15,6,(ROW($A$2:$A$7)-ROW($A$1))/($C$2:$C$7=$F$1),ROW(1:1))),"")

    enter image description here

    You have to adjust the formula for other team. For Sheet 2 just you need to refer Sheet 1 in formula like

    =IFERROR(INDEX(Sheet1!$A$2:$A$7,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$7)-ROW(Sheet1!$A$1))/(Sheet1!$C$2:$C$7=$F$1),ROW(1:1))),"")