Cross sheet reference in a formula is not working

Larissa

I am trying to change the status of a task when a row is added to a different sheet. I need 2 pieces of information to match, the course name and the module number. If those match, then I'd like the status to change to complete.

Here's the formula that doesn't work:

=IF(({Gen Ed CME/SDC/CME Support Team Mod... Range 1}, HAS(ANCESTORS([course number]@row), 2)), AND({Gen Ed CME/SDC/CME Support Team Mod... Range 2}, CONTAINS(PARENT([Task Name]@row))), "Complete")

The Gen Ed CME/SDC/CME Support Team sheet populates through a form. Once that form has been submitted for a particular course module, I'd like the status of the corresponding row in the Program Tracking Sheet to update. The Course Rubric should match the ancestors course number, and the Module Reviewed should contain the parent's Task Name.

Here are screenshots of both sheets.

I am new to formulas and would greatly appreciate any advice!

Thank you!

Kelly Moore

Whenever I’m stuck like this on a formula not working, I completely delete my ranges from my formula and re-insert to make sure I referenced the right column and that, when I was choosing the range, I actually inserted the column and not a single cell. Let’s double check that.

Kelly Moore

Hey @Larissa

Cross sheet references must be queried differently than queries within the same sheet. COUNTIFS can be used in cross sheet references to see if the criteria exists - if it exists then the count will be greater than zero.

=IF(COUNTIFS({Gen Ed CME/SDC/CME Support Team Mod... Range 1}, HAS(@cell, ANCESTORS([course number]@row)), {Gen Ed CME/SDC/CME Support Team Mod... Range 2}, HAS(@cell, PARENT([Task Name]@row))) > 0, "Complete")

I had to save/refresh to make the status appear.

As you build your experience with formula, consider the good practice of renaming the generically named smartsheet ranges to ranges that reflect your actual column names. This will make trouble shooting your formulas easier.

Does this work for you?

Kelly

Larissa

Thank you Kelly! I appreciate you answering so quickly.

I'm no longer getting errors, but the formula isn't working. It's not changing the status at all.

Thank you also for the advice and explanation.

Larissa

Kelly Moore

Hey Larissa

Let's work on that. I had the status changing in my sheet.

I did notice in your screenshot, it didn't work because I didn't have a matching course number when I built my two sheets off your screenshots. I had to add the GOVT-2301 to my second sheet to find the match. Please check you data to see if true conditions are present on the row you are testing.

If yes, as a temporary test to find what part is matching or not matching, remove one of the range-criteria pairs from your COUNTIFS. See if the status shows "Complete". If that one pair works, replace it with the other. This will tell us a lot in terms of pinpointing the problem.

For example

=IF(COUNTIFS({Gen Ed CME/SDC/CME Support Team Mod... Range 1}, HAS(@cell, ANCESTORS([course number]@row))) > 0, "Complete")

Larissa

Thanks for helping Kelly!

This one works:

=IF(COUNTIFS({Gen Ed CME/SDC/CME Support Team Mod... Range 2}, CONTAINS(@cell, PARENT([Task Name]@row))) > 0, "Complete", "false")

This one comes back false:

=IF(COUNTIFS({Gen Ed CME/SDC/CME Support Team Mod... Range 4}, HAS(@cell, ANCESTORS([course number]@row))) > 0, "Complete", "false")

Also, strangely, when I changed Range 1 and 2 to the column names, I got an #INVALID REF error and when I changed it back, it changed 1 to 4.

Kelly Moore

Hey Larissa

You can't just change the reference names- there's an editing process for cross sheet references. You may need to delete the range from the formula (one at a time), and re-insert it from the formula dialogue box. If you do this, before click the Insert Reference on sheet 2, change the name to the column name. If you're editing a name after the fact, write click on a cell that has the formula and choose Manage References. You can change it there. I always try to add the name on my initial insertion so I don't have to do all the rework on the back end.

Did you double check both sheets to see if the course name exists, exactly as you have it written on the sheet that contains this formula? See your screenshot above? The screenshot will return False, assuming your Range 1 is {Course Rubric}

Larissa

Thanks for the info about editing references. Yes, I added rows to the review sheet.

I appreciate your patience.

Kelly Moore

Whenever I’m stuck like this on a formula not working, I completely delete my ranges from my formula and re-insert to make sure I referenced the right column and that, when I was choosing the range, I actually inserted the column and not a single cell. Let’s double check that.

Kelly Moore

Hey @Larissa

As we're working to make sure that the hierarchy piece of your formula is working - realize that as written there is nothing in your formula that stipulates any activity on the particular child row is triggering a completion of task. As the formula stands now, the completion is based solely on the contents of your parent/grandparent rows. I am assuming this is as you intended.

Kelly

Larissa

Hi Kelly,

I deleted the references and started over and now it works! Thank you so much for your help!

I can't tell you how happy I am that this finally does what I want it to.

Kind regards,

Larissa

Kelly Moore

Phew! glad you got it working. If you decide to add more rules to your formula and you need any help, shout out to me. I'll be happy to try to help.

Kelly

Larissa

Thank you! I really appreciate all of your help!