Excel – Copy Data from One Sheet to Another if Criteria Met, Relative Rows

microsoft excelmicrosoft-excel-2010worksheet-function

I need to copy data from one worksheet to another based on criteria being met. On Sheet 1 (All Projects), information is collected in columns A – L. If "Project 1" has been selected in column M on sheet 1, then I want certain cells within that row to populate into a dashboard on Sheet 2. I used the following formula to capture this and it did work:

IF('All Projects'!M10="Project 1",'All Projects'!E10)

The problem is the "Project 1" text on sheet 1 will change between rows: The idea is to only have 1 row entry identified as "Project 1" at a time and that information will then pull into the dashboard.

For example, the formula above will work if "Project 1" has been selected in sheet 1 cell M10, but if instead "Project 1" is removed from M10 and placed in cell M11 then the formula will not work.

How do I make this formula relative to the changing row.

Thank you in advance for your help!

I added an image to make this easier to understand.

Best Answer

The problem is your formula is set to only look in cell M10. You need your formula to look through the entire column and find the match. Cybernetic.nomad mentioned vlookup, which would work. I personally prefer Index/Match formulas.

Index/Match combines two formulas to first:

Index the range of values you hope to show

then

Match the criteria against another range of values.

In your case, you want to Index the project name, and Match "Priority 1" as the criteria.

The formula in B4 is

=INDEX('All Projects'!B:B,MATCH(Priority!$A$3,'All Projects'!D:D,0))

Project Priority

This is what the formula is doing

=INDEX('All Projects'!B:B

Tells your formula that you want to return a value from sheet All Projects, column B. In this case, the project name

,Match(Priority!$A$3

This tells the formula what your criteria for matching is. In this case, it's the title in the red cells, or "Project 1". It is looking for a value in sheet Priority, cell A3.

,Match(Priority!$A$3,'All Projects'!D:D,0))

Now, the formula takes the criteria (A3 or "Project 1") and tries to find a match in sheet All Projects column Priority Project. (Change the column letter to suit your needs.

There are lots of guides on INDEX/MATCH online. It's a pretty flexible formula.

PS - The above solution should solve your issue. However, a personal recommendation would be to make your "Project List" an actual table by selecting all of the data and clicking on "Insert Table". Then, you can name the table under "Table Tools" and create references for your formulas, instead of using cell and columns. These are called Structured References.

For example: References

In this case, I'm still using INDEX/MATCH, but now all I need to do is start typing "Project List" (the name I gave the table with all the project info) and then I can select the table name, then select the column I want to reference.

For more information on Structured References, check out this site.

Related Question