Formula that crosses three sheets with multiple conditions

L_123

if the parties segregated to the "Public" use data don't need to change/edit data you could use a report filtered by person accessing the information. This way you don't need to worry about cross sheet stuff.

Daniel Arvesu

@L@123 The formula you put together towards the beginning of this string of posts did actually produce a value (on a small scale, and for one owner). I think you were on the right track, but not being able to discuss via a phone call or web session is proving difficult. Is there any way to talk through this?

As a note, I am going to try to reach out to the Smartsheet Pro Desk to see if I can communicate the formula request over the phone.

L_123

That's a good idea, you can also get in touch with your direct representative. in the meanwhile try this formula out.

=SUMIFS({PR-260-SD-HMH-Enterprise Solution Design Range 1}, {PR-260-SD-HMH-Enterprise Solution Design Range 2}, CONTAINS([Project Manager]@row, JOIN(@cell)), {PR-260-SD-HMH-Enterprise Solution Design Range 3}, @cell < 1) * INDEX({*** DO NOT SHARE *** PMO - Costing Informa Range 2}, MATCH([Project Manager]@row, {*** DO NOT SHARE *** PMO - Costing Informa Range 1}, 0))

https://app.smartsheet.com/b/publish?EQBCT=a1ec5df9d90844bfb4a160bbe46a8cf3

I've built this out to prove that the solution follows the parameters you've asked for(admittedly I only used one sheet though), if this one doesn't work we most likely have an issue with the ranges.

Paul Newcome

@L@123 I think the biggest problem is keeping Sheet B off of Sheet A. I wonder if we could use a JOIN/DISTINCT to pull the names from Sheet A into Sheet C and then use a SUMIFS on Sheet C?

Currently the owner(s) are not listed on Sheet C where we want the totals, and the totals on Sheet C are not listed out by owner. They are a total from multiple owners on Sheet A with different dollar amounts on Sheet B as a single grand total on Sheet C.

L_123

@Paul Newcome In my experience Distinct doesn't work across sheets if you have more than just a handful of values. I believe there is a hard stop of some sort built in to prevent it from running. Last week I had a formula not work because of this.

I think sheet c is the simplest part of this, just a simple sumifs referencing 2 columns with 1 criteria. It is the formula on sheet a that calculates the total/person where I am having difficulties. Something is going on that I'm not catching, if the formulas are correct, then it would have to be in the ranges.

@Daniel Arvesu Can you name your ranges as following so we can see exactly what we are looking at?

{Sheet Letter - Column Name}

Example for sheet "A", column "% Complete"

{A - % Complete}

Paul Newcome

@L@123 I was under the impression that we didn't want ANY totals on Sheet A. @Daniel Arvesu Can you confirm?

Daniel Arvesu

@Paul Newcome @L@123 That is correct, we do not want any calculations/totals/outputs occurring on any sheet except for Sheet C. I will work to get your other questions answered here in a few minutes, sorry for the delay.

Paul Newcome

Since we do not want any calculations occurring on Sheet A, I am going to suggest my earlier solution about making a restricted copy of Sheet A, using cell links to keep the copy matching the hours, then running the cross sheet formulas on Sheet C referencing the copy.

Daniel Arvesu

@Paul Newcome What if, in the middle of the project, a new task is added to Sheet A? Would it show up in the restricted copy Sheet A or would i have to have twice the effort in adding to both sheets?

Paul Newcome

Depending on the exact setup of Sheet A, there may be a way to use a Copy Row Automation to pull the new row to the sheet and use cross sheet references instead of cell links to pull the WHR.