Google Sheets - Conditional Formatting Based on Another Cell Using Formulas Tutorial
Вставка
- Опубліковано 23 сер 2024
- Video tutorial about conditional formatting based on another cell & conditional formatting using formulas. You will learn how to use custom formulas that produce logical test output for conditional formatting. Also includes percent change calculations & ABS function.
Google Sheets
www.google.com...
Website:
www.chicagocom...
THANK YOU! I have been trying to figure out something for a month. After the first 2 minutes of the video I found my solution.
Excellent!
Your process of building the formula with TRUE/FALSE is easy to follow and do.
Thanks for this video. I regularly watch your videos for any google sheets problems. And to be honest, I always got what I wanted. Actually I was looking for highlighting two different rows depending on one cell value, but I don't know to lock using $ before. That's why, It was highlighting only first row. But as soon as I locked, my both of rows got highlighted. Thanks you!!!
EXACTLY what I needed. Thank you, thank you And THANK YOU for just diving right in and solving the problem pronto. You had a clear video name and did that exact thing within the first few minutes. You are a rockstar and your video just saved me from throwing in the towel.....you're a lifesaver!!! THANK YOU!
Thank you so much for your sharing! you have helped me solve my problem that i was looking for the answer for the past 1 day.
Thank you- first time using Google Sheets and it was driving me crazy that I couldn't just click the cell like I can in Excel. This worked like a charm.
great knowledge. very useful.keep it going,,,learn Google spreadsheets
4:54 How to change color of 1 cell if the value is greater than the value in a different cell
Thank You miss. Was finding this for a week.
Thanks.... great video
Thanks Sir .....Great Tutorial ....
Great video...simple
Awesome video. Total was what I needed!! Thank You! Thank You! Thank You!
But all of these examples are based on pairs of cells. What if I want to condition format a group of cell values based on a single other cell value that is always changing, like an average?
Thank you! I would to know how to do this : I have five keywords filled in column a. I type a sentence in column b. If the sentence contains the keywords listed in column a, then the cell colour will be green. Looking forward to your help in this. Thanks in advance.
Hello. I am having problems to create a conditional formatting to format a line and a second conditional to format a collumn. Something like lines where the value is positive become bold and a collumn need to be green when is a value condition. They dont want to work together...
thanks
How do you change cell color to red if the number is smaller that the previous one? This formula needs to work with all the numbers below that cell. Red if b4
What if column D were a drop-down menu? I can't get cells in column A to be the right color based on cells in column D's values ...
Thank you, solved my issues
Very helpful, thank you!
Thank you!
What is the shortcut you use to send a certain formula down? This is something I can use frequently.
very good video , thank you
its very help full
thanks again.....
Why double click to copy the formula throughout the cells not working in my sheets??
How can i subtract by a dedicated number then see how much i have remaining. Like you have 20 points more to go until i ready 40 points.
I am trying to come up with a formula in google sheets to do something specific. Would you be interested in helping me? Basically I created a drag racing bracket. 2 competitors are set up in 2 columns where the stats of the race are displayed. (Reaction Time, Holeshot, Speed, ET) Currently the Winner is indicated by the last rows in each column and the conditional formatting I have set-up turns the cell green if the cell has a lower ET number than the other competitor's column. I want to have the formula see which column is the winner and then take 1 cell of that column (the persons name) and populate another cell on the bracket (to show the winner advanced). I can show you specifically how I have it now if you are interested...thanks for your time...
I seem to be able to get the conditional formatting in and even though I have got in my cell range only some of those cells within that range are changing colour if bigger than another number and not all the cells which are bigger. I dont know what im doing wrong?
In the last example, can you compare the cell against another one on a different sheet? (e.g. =D2>'Other Sheet'!$A$1) ?
Is its possible to make a group of cells turn a certain color if a checkbox is clicked?
Is there a way to copy the formulas like he does the true/false cells? Just by dragging them down or do I have to manually make every rule for every row. My example would be that I want to see if B7>B2 and B8>B3 ; B9>B4 and so on.
Is it possible to get a formula that will look at text in a cell and if it sees the right word it will fill out the cell beside it with todays date?
great video, thanks a lot!
Can you help ? I have created an expense income sheet. When an expense is made by credit care, I would like to the sum to subtract from the row which contains the running credit card balance as well as subtract from the row which contains the overall balance.
I am trying to figure out if I can do a conditional formating that will automatically change based on the value of another cell which also changes automatically. This is the cell that I want to link my conditional formating to =COUNTIF(A4:A1002,"") I have few other columns with =countif(C4:C1002,true). In column A I am adding data manually, that will change the value in the cell with the first formula for example to "100" I want to make the background of the cells with the second formula change colors from red to green when the number is closer or equal to the value in the cell with the first formula.
In every column with the second formula, there is a specific number of tasks which need to be done, that number is always changing and it is shown in the cell with the first formula
I'm new learning app script, but "How would you go about creating Conditional Formatting through App Script instead of creating it through the menu?" I created it but it keeps duplicating itself to build a new conditional format. How can I write it if it doesn't exist to not push another build.
I have a list of items categorized. Does the following cell auto populate based on the input.
Hi Could you tell me about Multiple functions How you count multiple text in row google Sheet. =SUM(COUNTIFS($AE$3:$BI$3,"L","SL"))
This is not working with lines, only cells..
I have the values by lines and try to check if A1 is equal to B1 and when i select A1 to X1 to compare, the conditional formating is appied to all cell in that line even if A5 is different from B5 becasuse the formula is only comparing A1 to B1
Can anyone help me?
thanks for your tutorial, also i'm creating a Dice machine that has randomized different values, but i want a to format a product value based on the principal dice. For Example:
10 sided dice (d10) that i did (i will say this cell is a [D10])
*1* its a Critical Fail (*Dark red*)
*2 to 4* is a Fail (*Red*)
*5-9* is Success (*Green*)
*10* is Grea Success (*Dark Green*)
Well the real dice is done but i wanted the Results of this one would be the same format as [D10] so what i tried was
Dark Red: $D$10==25=10
After this i don't know what i failed but few of formats doesn't show or are shadowed by the others formats.
if can give some advice it would be a great help, thanks for all:
heres the Dice Machine: docs.google.com/spreadsheets/d/10zdIOi2-LrUa0c2lnaNJliiVIJ3qCSR3wmZtRtx_N2o/edit#gid=959752359
can i replace a text based on condition of another cell? because all tutorial i found only change color based on condition from another cell.
To replace you would need a script. Otherwise you can use IF function ua-cam.com/video/hG5vKMb0Lpo/v-deo.html and get the results in a new column.
Beware of using Named Ranges in a conditional formatting formula. It will say "Invalid Formula". If you use cell notation it works fine.
Hi. New subscriber here. Im just curious if it is possible to put a automatically put the date today when I click on another cell. Because I use sheets for studying. For example I clicked A2, A1 will show the date when I last edited or click. What I do now is manually putting dates to track when I last studied a specific row. Thank you.
you can try
=today()
for today's date.
How do you make this true for multiple names?
=ARRAYFORMULA(IF(REGEXMATCH(C2:C,"LGA"),"Yes",""))
I have one Name "LGA" AND I need to add a few more to pull from the same colum (C2:C). How do you this?
try =ARRAYFORMULA(IF(REGEXMATCH(C2:C,"LGA|NBC|NFL"),"Yes",""))
@@ExcelGoogleSheets Thank you! I hope you know you are helping a lot of people :) This worked wonderfully. I couldn't find much about this particular function.
@@ExcelGoogleSheets Perfect!
One More Question... Below is my Formula.
=ARRAYFORMULA(IF(REGEXMATCH(COMPANY,"LMC"),"TEMPLATE 1",""))
This finds the Company "LMC" in my range and tells me which "Template" to use. I have Several Companies that use Different Templates.
I'd like to add more companies to this formula stating which template to use. Something like below (I know it's wrong but i wanted to demonstrate what i meant).
=ARRAYFORMULA(IF(REGEXMATCH(COMPANY,"LMC"),"TEMPLATE 1","")) +(REGEXMATCH(COMPANY,"MAD INC"),"TEMPLATE 2",""))
Is this possible with the Array / RegexMatch?
Would you be able to help? I want sheets to recognize Jon in column A, take column J dollar amount, and multiply . 75 into Column O. Would you be able to put a formula for that? Thanks
Nothing to do with formatting but I think . . assuming A1 has Jon, & J1 has 200.00 then in cell O1 enter the formula =if(a1="Jon",J1*.75,) . . & cell O1 should give the result = 150.00
🙏🏻
There is one thing you missed out what if you want the whole row highlighted not just a cell.
Didi I? :) ua-cam.com/video/GcAPzRGjunk/v-deo.html
Sorry I seen that after I watched this one. You are very good at your videos. I am learning a lot that I did not know
Thanks, but try to make it shorter, it can be done in half the time
hi could you please explain to how to put conditional formatting, if cell value is between other 2 cell values
Use AND function. =AND($A1>$B1,$A1
what about if the data changes as the sheet continues im trying to make it highlight when a payment is late compared to the date above it...
gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg😊gggggggggggggggggggggggggggggggggggggggggggggggg😉gggggggggggggggggggggggggggggggggg🙂ggggggggggggggggggggggggggg🤣gggggggggggggggggggggggggg😅gggggggggggggggggggggggggggg😆ggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg😅ggggggggggggggggggggggggggggggggggggggggggggg😆gggggggggggggggggggggggggggg😄ggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg
Based on your EXP. 2 sheet:
Imagine if you had all your expedition sheets in a single sheet and you want a cell to display the total money spent on Travel. How would you do that?
My case: I want a cell to display the sum of reward values won by Baden, and a separate cell to display the sum of reward values won by GriZzly. i.gyazo.com/02370b70d302813baa1f51b6ff7b3a10.png
Watch this video ua-cam.com/video/T98RwlndSZk/v-deo.html
why the hell did you change the negative to a positive IF, you are trying to find GREATER than 5% Just look at the row for UTILITIES. The Actual is OBVIOUSLY less than the Projected value. You just counted that as GREATER THAN 5% ---YOU JUST SCREWED UP YOUR DATA lol! Everything you are doing is the same thing over and over again from the 1st example. I want to see conditional formatting skipping that negative sign. ---you cheater! LOL!
Your videos are lengthy & explanations are stretched like chewing gum. Please reduce it to 5 min max for more views.