Interactive Conditional Formatting between Sheets - Google Sheets 

It's always so hard to find exactly what we're all looking for. Glad you found what you were looking for in this!

Awesome! I'm glad that this got you where you need to be!

Hi! It might depend on your situation a little more, but generally the set up and formula is the same. The hurdle here is how the conditional formatting uses indirect() to read across the sheets. Here is an example of rather than working with a list or filter it just uses one cell on the second sheet. If you change that single value it will change what's highlighted. docs.google.com/spreadsheets/d/1S5p4BmCKnp1daZrHgTc6RZwim3M1XBykrso6QNWya-0/edit?usp=sharing. I see you said "cells" though... so understanding your sheet and what multiple cells you want it to reference might change my answer. If you have a sample sheet I can take a look.

were you able to solve this? I have the same issue!

Hi. This could change slightly depending on how your sheets are set up, but here is an example (docs.google.com/spreadsheets/d/1LLvKF2u-J2UCOCMtTMNEuX44f0NsxVyziRf6-SSYb7Y/edit?usp=sharing) that would mimic students responding via a form into a sheet with your answers listed on the second sheet. The formula is similar to the video (=COUNTIF($D2,"*"&indirect("Answer Key!$B$3"))>0)) where we make a statement that returns true if the student answer matches your answer. You just need to make sure that the column that you use the conditional formatting custom formula on matches the look up in the indirect("Answer Key!$B$3") part. In this example it is looking up cell B3 on the answer key to check if it's correct. I included two examples in this. For number answers, true false, or multiple choice this would be very straight forward. If it's open ended this would be much more difficult since you would have to check for some partial string. In simple questions you might be able to handle this (like in the sample) but it's not likely to be consistent. For the red coloring, we use the same formula but rather than checking if the count comes back >0 we check if it =0. And then we also include a condition with no coloring if it's blank (this just prevents the whole column from looking red even when empty). Hope this helps!

@@theworkflow_ thank you so much!

You'll likely have to use an API at that point.

Hi Ruben, glad the video was helpful! Your question is quite a bit different than this video about conditional formatting, but I'm happy to help. Here is a sheet with a few different options and an explanation below. Hope this helps! (Sheet is in view only so make a copy to pay around with it) docs.google.com/spreadsheets/d/1z2QrACwVM9YZLSWbbbGbq5kYRoGvbit43qFWiZRlHRU/edit#gid=0 If you have your forms set up so each student can only submit one response, a really quick way to do this is to use Countif(). Basically make a list of the student emails on a separate sheet and use Countif() to count how many times the students email appears on the forms responses from form 1, then add that to form 2, 3, and 4. The down side of this is if you do not have the form restricted to one response only, you could get misleading results from students who submit a single form more than once. (this is on the sheet on the tab ' Completion Check #1 - count if' A more precise way is to use Vlookup() since this will return a response at the first time it finds the students email (i.e. it wont count more if the forms allow for multiple responses. The downside here is if the Vlookup() doesn't find a match it pushes an error, so you have to do some error handling. (this is on the sheet on the tab ' Completion Check #1.2 - vlookup' Alternatively, I thought it might be beneficially to you to know which of the forms each student is missing. So another way to look at this rather than just saying Yes or No if they completed ALL 4 forms. Is to lookup each form independently so you can more precisely follow up on students who are missing a form. This is how I do it at my job to make it easier to follow up with people on specifically what they have not submitted. This can be found on the tab 'Completion Check #2'. And to tie it back to the conditional formatting... you can easily format your completion check sheets conditionally if the cells say yes or no without having to go between different sheets like the video explains!

@@theworkflow_ Thank you so much. I will try this out! Have a restful day! Cheers!