Formula that crosses three sheets with multiple conditions

Daniel Arvesu

I think my hang up is with regards to how I am referencing the Task Owner. So let me see if I can lay it out for you to see if you can find where the issue is.

• I have a Task Owner(s) column in project plan Sheet A, with each cell in that column having the ability to select multiple names that I have setup within the column properties
• I have a Task Owner(s) column in Sheet B with each cell having the ability to select a single name out of a list that I have setup within the column properties. For a visual, the very next column is each of Task Owner's rates. So, list of names in a contact list column and a list of rate in a text column.

If I am referencing a specific "Task Owner" in the formula, do I need to reference a contact list in Sheet C? Can I reference that name out of any contact list (meaning pull from the contact list in Sheet B)? Or could I even just type out the name instead of referencing a cell in any of the sheets?

Paul Newcome

Are you able to provide some screenshots with sensitive/confidential information removed, blocked, and/or replaced with "dummy data"?

Daniel Arvesu

@Paul Newcome My sincere apologies for the delay, but I have went ahead and made a dummy Sheet A, Sheet B, and Sheet C, to hopefully paint a better picture for you. I really want to thank you for your help thus far. If we can make this work (which I think we have gotten to a point of semantics, which is encouraging), this would be a huge help for our organization and the amount of time spent to manually calculate this stuff out.

Alright, without further ado...

Daniel Arvesu

SHEET A (SAMPLE PROJECT PLAN) - With Columns "Owner", "Work Hours Remaining (WHR)", and "% Complete"

Daniel Arvesu

SHEET B (Sample Sheet that has Owners and Rates - All Samples)

Daniel Arvesu

SHEET C (Sample Dashboard) - I have created a dashboard that links and pulls a lot of data from our various projects, and I need to include an automated way to calculate "Projected Costs" based off WHR for each line item of a project

Paul Newcome

Can you manually enter the desired result in Sheet C based on current screenshots and explain exactly how you came to that conclusion?

You said that you want to base off WHR for each line item of a project, but in your Sheet C you only show a reference to the project as a whole.

Daniel Arvesu

Modifying SHEET A screenshot to assist better in providing my response. I have included a third task that is 100% complete.

Daniel Arvesu

SHEET C (Revised)

Daniel Arvesu

For a specific project, the formula should first look at the % Complete column to verify if the task is completed. If completed, it should NOT be included in the calculation. If the % Complete column shows less than 100%, next the formula should look at both the "Owner" column and "WHR" column, grab that WHR value FOR EACH OWNER, multiply that by the rates in Sheet B, sum it all up, and dump that value in Sheet C. So, when I go to close out a task, that should automatically drop from the calculation, and Sheet C should always show a real-time view of "Project Costs" based off off the "WHR" for each project.

So, to answer your question:

$300 = (1*$50) + [(2*$50) + (2*$75)]

Project Costs = ("Determine Any Outstanding Needs" Owner(s)* Rate(s)) + ("Follow-Up Review Meetings" Owner(s)* Rate(s))

Daniel Arvesu

@Paul Newcome Hi Paul, did the above help at all?

Paul Newcome

You are going to want to insert a text/number column into Sheet A and on a row by row basis calculate the amount based on the rates in Sheet B. Then pull the total from this helper column into your Sheet C.

To get your amounts on Sheet A, you are going to want to use a formula such as...

=SUMIFS({Sheet B Rate Column}, {Sheet B Task Owner Column}, FIND(@cell, Owner@row) > 0) * [Work Hours Remaining (WHR)]@row

Then on Sheet C you should be able to use something along the lines of...

=SUM({Sheet A Helper Column})

Paul Newcome

Sorry for the delayed response. Work got a little busy.

Daniel Arvesu

@Paul Newcome Sheet B is private, locked down and has sensitive information that we cannot include in a public project plan (Sheet A), which is why I am trying to tie it all into a formula that outputs one financial value in a third, public sheet (Sheet C). Is there no other way?

Paul Newcome

You could create a copy of Sheet A and use cell links to keep the WHR column up to date. Then do the calculations above on the copy of Sheet A.