Metrics between Multiple Sheets

Jenn W.

I have 2 Sheets that work together to track projects. Sheet A records the initial project for Group A. When the project is ready for Group B to take over, I have an automatic flow to copy the row over to Sheet B. They each have different milestones to reach.

I need to gather a specific time between a completion date listed on Group A's page and Group B's page, but when doing the move between sheets, the project rows no longer match. For example, if project on row 3 in sheet A is ready for group B and is moved over, it'll show up on row 1 on Group B's page. Then Row 1 of group A gets marked ready, and it'll so up on row 2 on group B's page.

Is there a formula that can be used that will capture this information by saying Netdays from sheet A and sheet B if the project names are the same?

Paul Newcome

Try using the syntax in the example. You shouldn't have to rearrange the order of anything. You should be able to just update column names and range references.

Paul Newcome

You would need to have a unique identifier on each row in Sheet A that carries over to Sheet B. Then you can use an INDEX/MATCH to grab the date from Sheet A and nest it in the NETDAYS function.

=NETDAYS(INDEX({Sheet A Date Column}, MATCH([Unique ID]@row, {Sheet A Unique ID}, 0)), [Sheet B Date Column]@row)

Jenn W.

Every project has a unique number that appears in both sheets. So, lets say Sheet A has columns named "Project Number" and "Test Finish". Sheet B has columns "Project Number" and "Paperwork Finish". I need to find the time between Sheet A's "Test Finish" and Sheet B's "Paperwork Finish". I wasn't able to make the above formula work, but maybe I'm not entering it correctly?

Paul Newcome

@Jenn W. Which sheet is the formula going on?

Jenn W.

Sheet B

Paul Newcome

In that case, try the same syntax as the above formula but make sure you are updating the cross sheet references to the indicated sheet/column and making sure the [Unique ID] column is changed to reflect the column you are actually using for the unique ID.

If you are still unable to get it to work, please provide a screenshot of the formula actually in the sheet similar to the screenshot below.

Jenn W.

That is an example of Sheet B. Test Sheet A Range 1 is the project number column in Sheet A and Range 2 is the Test Finish column.

Sheet A:

I'm sure I'm just entering the formula wrong.

Paul Newcome

Try using the syntax in the example. You shouldn't have to rearrange the order of anything. You should be able to just update column names and range references.

Jenn W.

Thank you! It's working now! I really appreciate it!

Paul Newcome

Happy to help. 👍️