Formula Referencing Another Sheet

Teresa Lai

Hi,

I wrote a formula and each of the column will be referencing from another sheet whereas it shows unparseable. Can you give some suggestion on how to modify?

• Formula is in Sheet A
  • =COUNTIFS({Person Accountable}:{Person Accountable}, CONTAINS(@cell, "System Manager"), {Done}:{Done}, @cell=1, {Inapplicable}:{Inapplicable}, @cell <>1)
• I want to do calculation on how many Checkbox in Done Column is checked off by "System Manager" and this must exclude the actions with Inapplicable column is checked off
  • "System Manager" is under Column Person Accountable in Sheet B; "Done Column is in Sheet B and Inapplicable Column is in Sheet B

Kelly Moore

Hey @Teresa Lai

Just a slight tweak is needed with your syntax. In a cross sheet reference the entire column (or range) is contained within the double brackets. You don't use the colons. I also corrected the syntax within your CONTAINS function. If the response within the cell equals System Manager you don't need CONTAINS but could instead use @cell="System Manager" to simplify your equation.

COUNTIFS({Person Accountable}, CONTAINS("System Manager", @cell), {Done}, @cell=1, {Inapplicable}, @cell <>1)

Does this work for you?

Kelly

Teresa Lai

Hi Kelly, thank you it works! How about I want to reference a specific range in another sheet?

For example, the specific range is between row3 to row12 under Person Accountable column? The formula I wrote below is unparseable. Or should I select the range I want to specify when referencing from another sheet?

=COUNTIFS({Person Accountable}3:{Person Accountable}12, CONTAINS("System Manager", @cell), {Done}, @cell = 1, {Inapplicable}, @cell <> 1)

Kelly Moore

Hey Teresa

You cannot use the syntax range:range when referencing cross sheets. This is what I corrected last time and will always produce an unparseable error. The [column name]:[column name] syntax is only when working on the same sheet.

There are multiple ways to reference a specific subset of cross sheet rows. Can you share the source sheet and the destination sheet (remove sensitive info). I would like to see what rows 3- 12 pertain to. Specifically I wondered if these were child rows. Be sure to include the column names, if possible, in the screenshots so I can build the formula specific to your sheets

Teresa Lai

Hi Kelly,

Thank you! Please see below for the source sheet. Row3-12 are child rows in Phase A to calculate how many actions are complete, not complete or inapplicable for a specific role (under Person Accountable Column). And the calculation will be for Phase B, Phase C...etc. In addition, there is filter used (in Apply To Column), not sure if this might impact on our formula or not.

In destination sheet, this is the desired table to display the calculation from source sheet.

Kelly Moore

Hey Teresa

I have an approach to a solution but it will require the addition of a helper column in each sheet. In the destination sheet, you will need a Phase column. It doesn't appear that this sheet is set up in Parent/Child hierarchy so you will have to manually add the Phase name to each row.

In the source sheet add a checkbox column that will indicate if the row is a Parent row or a Child row, if you don't have one already. I typically called this column Parent or ParentIndicator on my sheets. The formula below will put a checkbox in every parent row. You can also use this column to conditionally format your Parent rows, if you don't have that already done.

ParentIndicator

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, 1)

Unfortunately in cross sheet references you can't directly call out CHILDREN. However, with your Phase designation and knowing if a row is a Child row or not, we can use that in the COUNTIFS formula. You will need to create a COUNTIFS for your Complete column, your Incomplete column and your Inapplicable column.

Complete column

=COUNTIFS({Source Sheet Person Accountable}, Function@row, {Source Sheet Phase}, Phase@row, {Source Sheet Status}, "Complete", {Source Sheet Parent}, 0, {Source Sheet Inapplicable}, 0)

Incomplete column

=COUNTIFS({Source Sheet Person Accountable}, Function@row, {Source Sheet Phase}, Phase@row, {Source Sheet Status}, "Incomplete", {Source Sheet Parent}, 0, {Source Sheet Inapplicable}, 0)

Inapplicable column

=COUNTIFS({Source Sheet Person Accountable}, Function@row, {Source Sheet Phase}, Phase@row, , {Source Sheet Parent}, 0, {Source Sheet Inapplicable}, 1)

Will this work for you?

Kelly