Thank you for this! I found the INDIRECT to link the other sheet but was struggling for something that worked. Your use of the FILTER did the trick to help me mark off items in a list for a draft as each pick was made.
Gosh, I really got to thank you. I was finally able to do what I wanted with my sheet. I've been through half a dozen videos and just as many text tutorials trying to figure this out, leaving just stumped. You explained everything clearly, provided examples, and didn't direct us to an entire playlist of other videos just to get the point. Thank you so much, I was ready to pull my hair out xD
I'm sure this is late, but I needed this and was struggling for over a full 2 days, on this one line - thank you so much for making this video, you've made my week! +1 Sub :)))))))
You did amazing! I was looking for this and tried couple times to figure out how to make it work and after a few hours trying, I realized I had the wrong column reference, lol. Thank you!
hi, how to do if i check off a checkbox cell in sheet 1 and another cell linked on sheet 2 is highlighted? im not filtering any checkboxes, just checking off one checkbox
I’m trying to create conditional formatting that highlights a cell on the annual calendar tab if there was an entry made on that date in the monthly tab.. I can’t seem to get the formula right, no matter what I try! Help? Thanks in advance!
Thank you for this! I want my students to enter answers on one sheet and if it matches with correct answers on second sheet it turns green. Red if wrong. How can I do this?
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!
By any chance would you happen to know how to conditional format specific cells based on *other individual cells from another sheet. This works for existing lists/filter, i am still struggling to figure out how to do it for one cell at a time
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.
Thank you for this video. It is very helpful. I am struggling with a formula to automatically identify duplicate students in 4 different tabs. Students are completing 4 forms that feed into one sheet. Is there an easy way to identify which students have completed all 4 forms? Any help is greatly appreciated.
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!
Thank you for this! I found the INDIRECT to link the other sheet but was struggling for something that worked. Your use of the FILTER did the trick to help me mark off items in a list for a draft as each pick was made.
Awesome! I'm glad that this got you where you need to be!
Gosh, I really got to thank you. I was finally able to do what I wanted with my sheet. I've been through half a dozen videos and just as many text tutorials trying to figure this out, leaving just stumped. You explained everything clearly, provided examples, and didn't direct us to an entire playlist of other videos just to get the point. Thank you so much, I was ready to pull my hair out xD
It's always so hard to find exactly what we're all looking for. Glad you found what you were looking for in this!
I'm sure this is late, but I needed this and was struggling for over a full 2 days, on this one line - thank you so much for making this video, you've made my week! +1 Sub :)))))))
You did amazing! I was looking for this and tried couple times to figure out how to make it work and after a few hours trying, I realized I had the wrong column reference, lol.
Thank you!
Thank you very much! keep going. you deserve more subscribers !
i am so greate ful buddy...you deserve million subscribe
This helped a lot! Thank yo so much
How can I use this to validate whether a submission was made in a sheet from google forms when I mark it complete on a different sheet?
Thanks, remembered that it had to be done with indirect, but forgot where/how to apply it.
Thank you! This helped so much.
hi, how to do if i check off a checkbox cell in sheet 1 and another cell linked on sheet 2 is highlighted? im not filtering any checkboxes, just checking off one checkbox
Can this method be adapted to do stock levels in a sheet, based on quantity used in one column and part number in another column on another sheet?
I’m trying to create conditional formatting that highlights a cell on the annual calendar tab if there was an entry made on that date in the monthly tab.. I can’t seem to get the formula right, no matter what I try! Help? Thanks in advance!
were you able to solve this? I have the same issue!
Thank you for this! I want my students to enter answers on one sheet and if it matches with correct answers on second sheet it turns green. Red if wrong. How can I do this?
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!
how to have same conditional format rules between sheets in Google sheet
You'll likely have to use an API at that point.
By any chance would you happen to know how to conditional format specific cells based on *other individual cells from another sheet. This works for existing lists/filter, i am still struggling to figure out how to do it for one cell at a time
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.
🙏
Thank you for this video. It is very helpful. I am struggling with a formula to automatically identify duplicate students in 4 different tabs. Students are completing 4 forms that feed into one sheet. Is there an easy way to identify which students have completed all 4 forms? Any help is greatly appreciated.
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!
That's not a different sheet.
🙏🏾🙏🏾