A note for people using Excel (or Office) in a locale that uses commas as decimal separator, don't forget to use semi-colon in the GET.CELL call to avoid "#value" errors :) Thanks for the tip that was really helpful!
Great tutorial, does exactly what I needed & saved me a lot of time & brain power. It works perfectly with the style colours that I have used on over 3 years worth of data & now want to use for another output/purpose, it even refreshes automatically when I apply or un-apply the style colour, thank you 😎
I just saw your once video and simply loved it for the simplicity and for its ability to solve the problem. Then I had another problem I searched for solution and guess what... your video (this) popped up again.... needless to say it was exactly what I was looking for.... and then without delaying I subscribed for the channel. Absolute appreciate your work....
Wow - that is an amazing trick. I have bookmarked this for future use and thank you very much for your free tutorial. I will DEFINITELY use this again!
Thanks Chester. This is a brilliant tutorial. But I tried this and realized this doesnt work on cell colored using conditional formatting. Is there a workaround to that?
Super useful! Nice video. Rather than the format painter, I just get in the habit of hitting Ctrl-Alt-F9 if I update any of the shading/fill colors. Ctrl-Alt-F9 tells Excel to recalculate all of the formulas inclusive of that property.
Spent days searching blogs and vids before i found your "work arounds" - Well done!! For those finding that other cells not being updated, I found that if your press the "filter" pull down and press OK, it 'refreshers' & updates the other cells... worth a try?
Hi, Can you help me please, I have 2 sheet, the other sheet is to copy the main sheet, but I want to make it not copy the data from the main if the cell color is gray. can you help me how to do it please.. Thanks
Hi, thanks for the video! I have a problem same with others, Everytime I drag down the cell it only returns 1 value (based on the original cell where its color is based on). I already removed the'$' on the formula.
This is fantastic, works great! I used the concept to create a table that used row color to group together rows with the same key values to make it more legible. I've tried every VBA method to do this before and while it would work it is so much more complicated than this. It gives me all kinds of ideas to use the Name Manager in the future. Thanks for the short but really valuable lesson! 💪
Hi Chester. Thank you for this, another brilliant workaround. I have a question regarding Colors in conditional formatting. This approach doesn’t seem to capture the cell background Color for those cells that have been formatted by a condition. Is there a way to capture these as well?
Can you uss this function in connection with a cell that is conditionally formatted? I have a column of record ID with corresponding where duplicate IDs are conditionally formatted. I need an IF statement to find the cells conditionall formatted and extract the corresponding value. The method here does not recognise the conditional formatting of duplicate cells. is there a way?
This is fab! It works on the pre-set colours. I tried to use it on conditional formatted colours it doesn't quite work. For example, if I colour a cell grey, it returns "15"; but if the cell has no pre-set colour but was conditional-formatted as grey, this formula returns "0", same value as other cells that are the blank default cell colour. Do you know a way to obtain the colour codes for the conditional formatted colours?
I’m loving all your tutorials Chester! Thank you so much for explaining things so well 🤗. I created a color coded, 8 items, drop down list in one of the cells and I would love to make the cells to the right side of this one to take on the same color/pattern (achieved with conditional formatting) of the selection of that cell on the left. The right side columns have their own drop down lists but I made them colorless in order to duplicate the colors/patterns contained in the other one. Is this even achievable?
Brilliant Idea. I'm sorry if i dont see your video fully or another your video. how we set this formula (an IF based on cell colour), but in vertical area? because your formula is working through horizontal area. thanks
Thanks for this. I have tried it but the value I get for all cell colours is zero. Have followed step for step. Any chance the code for cell colour in get.cell has changed from 38? I am using excel (MS Office suite) on a Mac. Any chance that is why it isn't working for me? Thanks
Dear Chester, thank you for the video, I have problem getting different values for each colour. If the cell has a value and then i delete the value refresh the cell then only it shows different numbers, if I drag down the formula it does not work, is there something you would recommend? thank you
How do I use a coloured cells's value itself to highlight secondary duplicates like finding colpured cells value first then using that value to highlight another set of duplicates? Kinda like highlighting secondary uplicates after finding the first
Thank you so much. My only problem is that when I change the color of any cell. The formula does not update automatically. I would be thankful, if you can suggest any solution. Thank you so much. 🌟
is there a way to get a "True" or "False" value by comparing the colour of 2 different cells? For Example, if Cell A1 and B1 are both filled in with Red then cell C1 should say "Match" or "True"?
Thank you Chester! I tried your method, but regardless what color I have, when I type in "=CELLCOLOUR", it returns the same value to every single color. For example, I had cells in yellow, green and red, but number 6 is returned for all the colors. Would you please advise on this? thank you very much!
Do you know if you can do this with Conditional Formatting? The tip works great for fields that have been coloured by hand, but i can't get it to work for Conditional cells.
Hi Dave. It won't work with conditional formatting unfortunately. However, the conditional formatting must be based on a test, so all you do is use the same test in the IF statement. No need to use colour as your IF test.
Great Information - That being said: the usage of Application.CalculateFull will drive the recalculation desired. My challenge was a reconciliation sheet where I wanted to mark the cleared cells "yellow" and then recalculate a dynamic balance. Downside is that I couldn't get Sumif() to accept the CellColor=6 reference as input criteria. So created a hidden cell with =IF(CellColor=6,"X","") to drive the SUMIF, but as you discovered, the cell formula will not respond on color changes, and there doesn't appear to be a worksheet event that format changes will trigger [directly]. In the end, because the sheet HAD to be saved as XLSM, simply created a macro to toggle cell color and attached it to the worksheet as Ctl+Shift+"X" (we are reconciling here) and added Application.CalculateFull at the end of that macro. That drove a keyboard based color toggle on that worksheet that recalculated to change the hidden cell "X", and dynamic recalculation. (Hello Microsoft - an event on the worksheet cell format changes is in order) Your input on color sensitive formulas was invaluable - Thanks.
Nice video Chester! I was actually looking for another kind of function, can you help me out? I want to return the cell's text on a given interval, if that cell have a specific color. For example, if only one cell from A1 to A10 is red, i want the function to return whatever is written in that red cell. Is it possible? Can you help me out?
very good, thanks a lot, I have a problem on this: when I save it and close the excel and open that file again, the defined formula doesn't work :( would you help me please. ( I save in xlsm format ) xlsx also doesn't work.
Thank you for the tutorial! However I noticed that if the color comes from conditional formatting (for example for duplicate amounts) the get.colour won't work :/ Any way around that?
It works great, but want to use the workbook as a templete. Cleared all colors back to white cells, but even if the colored field is back to white, the cel color formula keeps naming the wrong color. Is like the formula doesn't auto refresh. I tried the refresh icon, but it didn't update either
Very Interesting, however I have a question and I wonder if you can help: I don't need to apply discounts, but instead i want it to simply count from Col. A to Z (for example) and display 24 in the end result (I use this to create work planner, so each colored cell represent 1 hr of work), Not sure if this is possible, and thank you in advance
![[FULL EP.73] เปิดบ้าน 70 ล้าน "แป้ง Zbing" ยกมาทั้งญี่ปุ่น l เคหสถานบานปลาย l One Playground](http://i.ytimg.com/vi/YBnJhTt07Pg/mqdefault.jpg)
A note for people using Excel (or Office) in a locale that uses commas as decimal separator, don't forget to use semi-colon in the GET.CELL call to avoid "#value" errors :) Thanks for the tip that was really helpful!
Thank you!!!!! You saved me hours of manual work to look for specifically colored cells in large file.
Exactly what I needed! TH-cam is the best education... Thank you
Very helpful. This turned an impossible task into a simple one.
Great tutorial, does exactly what I needed & saved me a lot of time & brain power. It works perfectly with the style colours that I have used on over 3 years worth of data & now want to use for another output/purpose, it even refreshes automatically when I apply or un-apply the style colour, thank you 😎
I just saw your once video and simply loved it for the simplicity and for its ability to solve the problem. Then I had another problem I searched for solution and guess what... your video (this) popped up again.... needless to say it was exactly what I was looking for.... and then without delaying I subscribed for the channel. Absolute appreciate your work....
thank you thank you thank you thank you!
I have been looking for a way to do this on and off for the past 5 years.
Thank you again!
Wow - that is an amazing trick. I have bookmarked this for future use and thank you very much for your free tutorial. I will DEFINITELY use this again!
You are welcome!
@@ChesterTugwellhow can extract color code using the contional formating.. this just work if you actually use the color only
Thanks Chester. This is a brilliant tutorial. But I tried this and realized this doesnt work on cell colored using conditional formatting. Is there a workaround to that?
Did you ever find a solution to this?
I would be interested in that as well
Super useful! Nice video. Rather than the format painter, I just get in the habit of hitting Ctrl-Alt-F9 if I update any of the shading/fill colors. Ctrl-Alt-F9 tells Excel to recalculate all of the formulas inclusive of that property.
Thank you SO much for putting together this video. Fantastic!!!
Spent days searching blogs and vids before i found your "work arounds" - Well done!! For those finding that other cells not being updated, I found that if your press the "filter" pull down and press OK, it 'refreshers' & updates the other cells... worth a try?
Thank you very much Chester........ you make my work much easier......
Chester that is some great outside the box thinking. Keep up great work. 👍⭐
Outstanding! Thank you!
Excellent...
Very step by step explanations...
Keep up the good work bro.....
Thanks thanks thanks....
You are actually the greatest of all time
God bless you.. this works perfectly.
took me forever to find this, thank you so much for the video!!!
Is there a way to make this work with conditional formatting colours? The ifstatements don't seem to like conditional format colors
Great and useful tip Chester. Thanks
Brilliant!! This is exactly what I was looking for!
Excellent tutorial, this saved me a great deal of manual sorting!
This is awesome, thank you! I've been trying to solve this problem for months! Appreciate you sharing this info!
Thanks very much for the tutorial !
Hi, Can you help me please, I have 2 sheet, the other sheet is to copy the main sheet, but I want to make it not copy the data from the main if the cell color is gray. can you help me how to do it please.. Thanks
This was incredibly useful. Thank you so much for sharing!
Thank you so much🙏🙏worked like a charm
Thank you! Very useful....!!! Appriciated
Hi, thanks for the video! I have a problem same with others, Everytime I drag down the cell it only returns 1 value (based on the original cell where its color is based on). I already removed the'$' on the formula.
Thank you so much, this is so helpful to me.
Works wonders. Thanks a million for this little trick. Makes a world of difference. :-)
You're welcome!
Works like a charm... Thanks!
Thanks man, saved me some time!
Thanks Brotha!
Thanks Chester. This is what I have been looking for. You just made my work very easy.
Fanstastic!! Thanks a lot!!!
this is so helpful!!! thank you :)
Thanks for this tutorial
Hello, sorry if you answered this already, but can you do the same but with FONT color instead of cell color? thank you!!
Brilliant! Very helpful!
Glad to hear it!
Excellent. Thank you!
This is fantastic, works great! I used the concept to create a table that used row color to group together rows with the same key values to make it more legible. I've tried every VBA method to do this before and while it would work it is so much more complicated than this. It gives me all kinds of ideas to use the Name Manager in the future. Thanks for the short but really valuable lesson! 💪
This was geniously done! Thanks
Hi Chester. Thank you for this, another brilliant workaround. I have a question regarding Colors in conditional formatting. This approach doesn’t seem to capture the cell background Color for those cells that have been formatted by a condition.
Is there a way to capture these as well?
This was the exact question I was coming on ti ask. Did you ever get a response?
Thank you for the tip. Saved me lots of time!
Can you uss this function in connection with a cell that is conditionally formatted? I have a column of record ID with corresponding where duplicate IDs are conditionally formatted. I need an IF statement to find the cells conditionall formatted and extract the corresponding value. The method here does not recognise the conditional formatting of duplicate cells. is there a way?
Did you find the solution? I am struggling with the same situation.
I have the same problem... Do y'all find a solution?
Cheers for this!
Great work!
Thanks!!
Nicely done.
Thanks!
Works 4 me, but but instead of a comma after "38" I have to put a semicolon. 10X!!!
Sadly doesn't work on google sheets, any recommendations? Thanks
Thank you for your help sir
Brilliant. Saved a lot of time..
OG
thank you man
Good stuff, thanks!!!
Thank you, saved a lot of time
Thanks heaps
This is fab! It works on the pre-set colours. I tried to use it on conditional formatted colours it doesn't quite work. For example, if I colour a cell grey, it returns "15"; but if the cell has no pre-set colour but was conditional-formatted as grey, this formula returns "0", same value as other cells that are the blank default cell colour. Do you know a way to obtain the colour codes for the conditional formatted colours?
This is VERY creative!
Fantastic .. THANK YOU
Hi, thanks for the simple solution. Just to add, the same file should work with Binary format also.
I’m loving all your tutorials Chester! Thank you so much for explaining things so well 🤗. I created a color coded, 8 items, drop down list in one of the cells and I would love to make the cells to the right side of this one to take on the same color/pattern (achieved with conditional formatting) of the selection of that cell on the left. The right side columns have their own drop down lists but I made them colorless in order to duplicate the colors/patterns contained in the other one. Is this even achievable?
Thank you so much!
life saver!! thank you!!
Does Not Work,What Version Excel are you Using
=GET.CELL is for English versions of Excel, it is different in other languages
Brilliant Idea.
I'm sorry if i dont see your video fully or another your video. how we set this formula (an IF based on cell colour), but in vertical area? because your formula is working through horizontal area. thanks
Hi. Could you make a formula - if you change the color of cell, next cell to it, will show todays date. Thank you
Perfect so Brilliant idea👍👍👍
Thanks for this. I have tried it but the value I get for all cell colours is zero. Have followed step for step. Any chance the code for cell colour in get.cell has changed from 38? I am using excel (MS Office suite) on a Mac. Any chance that is why it isn't working for me? Thanks
This is amazing! Can this be used to count the number of cells in a row that are the particular target background color? If so, how?
i have the same question
You should be able to do the same thing except replace the countif, for the if statement.
Is there a way to do exactly that, but based on the TEXT colour instead of the CELL colour?
Thanks for the info.
asking the same question
Dear Chester, thank you for the video, I have problem getting different values for each colour. If the cell has a value and then i delete the value refresh the cell then only it shows different numbers, if I drag down the formula it does not work, is there something you would recommend? thank you
How do I use a coloured cells's value itself to highlight secondary duplicates like finding colpured cells value first then using that value to highlight another set of duplicates? Kinda like highlighting secondary uplicates after finding the first
This is awesome. Thanks for the help!
Thank you so much. My only problem is that when I change the color of any cell. The formula does not update automatically. I would be thankful, if you can suggest any solution. Thank you so much. 🌟
4:57.. he talked about that
is there a way to get a "True" or "False" value by comparing the colour of 2 different cells? For Example, if Cell A1 and B1 are both filled in with Red then cell C1 should say "Match" or "True"?
Thank you Chester! I tried your method, but regardless what color I have, when I type in "=CELLCOLOUR", it returns the same value to every single color. For example, I had cells in yellow, green and red, but number 6 is returned for all the colors. Would you please advise on this? thank you very much!
have you saved your file as a macro-enabled worksheet? that worked for me as soon as I did that
Thanks Mr Tugwell...awesome 👌
Is it possible a vbn code to refresh calculation of a vbn created formula? thanks
Learnt ❤
Do you know if you can do this with Conditional Formatting? The tip works great for fields that have been coloured by hand, but i can't get it to work for Conditional cells.
Hi Dave. It won't work with conditional formatting unfortunately. However, the conditional formatting must be based on a test, so all you do is use the same test in the IF statement. No need to use colour as your IF test.
@@ChesterTugwell How can this be done when your test is for duplicate values?
@@ChesterTugwell I am trying very hard how to figure this out with an IF Top 5. Any pointers or other videos you've done that may be helpful?
Great Information - That being said: the usage of Application.CalculateFull will drive the recalculation desired.
My challenge was a reconciliation sheet where I wanted to mark the cleared cells "yellow" and then recalculate a dynamic balance. Downside is that I couldn't get Sumif() to accept the CellColor=6 reference as input criteria. So created a hidden cell with =IF(CellColor=6,"X","") to drive the SUMIF, but as you discovered, the cell formula will not respond on color changes, and there doesn't appear to be a worksheet event that format changes will trigger [directly].
In the end, because the sheet HAD to be saved as XLSM, simply created a macro to toggle cell color and attached it to the worksheet as Ctl+Shift+"X" (we are reconciling here) and added Application.CalculateFull at the end of that macro. That drove a keyboard based color toggle on that worksheet that recalculated to change the hidden cell "X", and dynamic recalculation. (Hello Microsoft - an event on the worksheet cell format changes is in order)
Your input on color sensitive formulas was invaluable - Thanks.
Nice video Chester!
I was actually looking for another kind of function, can you help me out?
I want to return the cell's text on a given interval, if that cell have a specific color. For example, if only one cell from A1 to A10 is red, i want the function to return whatever is written in that red cell.
Is it possible? Can you help me out?
GET.CELL trick won't work if the cell is filled using conditional formatting
Great job!
Really good! Thank you for the great video!
Glad you liked it!
THANK YOU MY GUY
Brilliant! Thanks a ton!
You're welcome!
Thanx
Most of the time I would use this with conditional formatting but it doesn’t detect it. It only brings back the original background color.
excellent
very good, thanks a lot, I have a problem on this: when I save it and close the excel and open that file again, the defined formula doesn't work :( would you help me please.
( I save in xlsm format )
xlsx also doesn't work.
Thank you for the tutorial! However I noticed that if the color comes from conditional formatting (for example for duplicate amounts) the get.colour won't work :/ Any way around that?
It works great, but want to use the workbook as a templete. Cleared all colors back to white cells, but even if the colored field is back to white, the cel color formula keeps naming the wrong color. Is like the formula doesn't auto refresh. I tried the refresh icon, but it didn't update either
To use font colour instead of cell colour, use 24 instead of 38
Very Interesting, however I have a question and I wonder if you can help: I don't need to apply discounts, but instead i want it to simply count from Col. A to Z (for example) and display 24 in the end result (I use this to create work planner, so each colored cell represent 1 hr of work), Not sure if this is possible, and thank you in advance
Great idea!
I have no name manager tab. Any suggestions as what to do??