This is FANTASTIC! I've been wanting to do this for years! I know you use #38 here, and having watched other videos of yours, you say that other numbers do other things. Where can I find a list of these things please?
Thank you soooooo much Chester! I've been working with a list for some time and was asked for a tally. It just happened to have three different items that I color coded but still had to count. I knew there had to be an easier way. I LOVE EXCEL!!!!
38 is the type_num that returns the cell index. For more on this, you can check out "Excel 4 Macro" functions and learn about all of them and how they work.
How can count the number of same colored cells in a row? Let's say conditional formatting turns some cell's background to Green in each row. How can we automatically get the SUM of the those Green colored cells in each row? My purpose is to Find all records which have 4 or more green cells. Hope it makes sense.
Hy! Awesome video, might apply it in the future, but there is one thing. It looks like it does not work in case of conditional formatting. Is it any way to do it? Should I maybe just use a different attribution number instead of 38? Thanks in advance!
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"?
Can we make it automatically by vba? I tried by vba function target.calculate, but it doesn't work at all. I used to have meeting, and some cells coloured with green for complete parts to assembly, yellow for partial complete, and red for not complete at all. When I was asked how many red, yellow and green, I use that formula, but if some cells color changed. That function need to be recalculate. Thank you very much for your paying attention
Is it possible to change the background color of a shape, based on the value in another cell? For example, let's say I have a cell with a percentage, and I want the background color of a shape to change based on percentage ranges within that cell. So, if the percentage is 30% or less, the background of the shape would turn to red. If it was between 60% and 31%, it would change to yellow. And then if the cell contained a percentage of 61% or above, it would be green. Is it possible to do this without using VBA?
It's not working for me... is it because the cells I'm wanting it to sum have background determined by a conditional formatting? How can I still use this if the cell has a conditional format?
Chubak Miaouw yes its wonderfull... but i want the figure change when i change the colour, how to do that...don’t want to keep on copy and paste the formula..
I have looked everything in google and haven't found an easy way, and I would like to have one, how Microsoft hasn't thought of this? I have web excel that I would like to count the colors cell as we add and remove names and backgrounds but haven't found a way yet... this was helpful but still cant help me :(
What do you know about the function Cell()? That is supposed to give a number for Cell("color",D4). I haven't figured out yet although Cell("Format",D4) does work, as well as most of the other options "Row", "Col", etc.
Why does my Excel program just shut itself off the moment I type in or choose =CellColour like at 1:50? Even if I have more than 2 tabs of Excel open, it just immediately closes the programs.
I did the sum CellColour formula & it worked fine. I then linked another spreadsheet to the totals. Now when I open the spreadsheet with the CellColour formulas in it & add more lines to the data, I get a #BLOCKED message coming up when refreshing the cells. Why is this? I have a registered version of Office365 & am connected online. I removed the links & still get the #BLOCKED error message when I try & update new cells.
The only way you could have the colors updated if you save a color keys on the sheet. For example, list all of the colors you will be using, then copy the color you will need from the list every time instead of using the menu bar.
Suuupeeeerrr!!! I remember that I spent a lot of time looking for a way of how doing this, at last I had to use vba and even if I couldn't get what I expected but this is awesome. One thing, I didn't understand very well why using number 38 in the function?
38 is used to get the color of the cells. The function is used to get various informations of a cell and 38 is to get the color. Other numbers will get other informations. I hope this helps
With Get.Cell you can retrieve data about a cell including background colour. The 38 is the index number for the cell's colour. Other numbers retrieve other info about the cell. Microsoft documentation on this is sparce.
Question for you.. first of all thanks for the trip. Wondering if it is possible to assign a color a value? That is to sat when I highlight a cell that I can associate that color with a value always equaling 10 as an example.. best, Edward
In the conditional formatting.. highlight cells....text that contains...10..... then instead of "light red fill with dark red text" click the drop down choose custom format... press okay.... choose tab fill.... choose the color...choose ok.... then okay
Thank you so much for this. I had been searching endlessly for a solution without VBA and this works perfectly .
It's not great, it's brilliant !!! thanks et congrats for this elegant and original solution !
This is FANTASTIC! I've been wanting to do this for years!
I know you use #38 here, and having watched other videos of yours, you say that other numbers do other things.
Where can I find a list of these things please?
Thank you soooooo much Chester! I've been working with a list for some time and was asked for a tally. It just happened to have three different items that I color coded but still had to count. I knew there had to be an easier way. I LOVE EXCEL!!!!
Agreed about the cell color... You could've showed us what color fill you chose so we could have followed along from end to end.
Thank you, this totally solved my problem, even though I still not quite understand how the function actually works.
Glad it helped.
So informative vid but I've two questions:
1-why 38?
2-why this doesn't work with conditional formatting?
Thanks for help me guys!
😊
yeah, kinda useless if it doesn't apply to conditional formatting (implies you're doing everything manually anyway)
@@DNut10 what is conditional formatting? Is this something that I can select?
same question why 38 ?
Thank you for your tutorial! it's literally useful.
Thank you, very easy with no macro, but why did you choose 38 ?
38 is the type_num that returns the cell index. For more on this, you can check out "Excel 4 Macro" functions and learn about all of them and how they work.
How can count the number of same colored cells in a row? Let's say conditional formatting turns some cell's background to Green in each row. How can we automatically get the SUM of the those Green colored cells in each row? My purpose is to Find all records which have 4 or more green cells. Hope it makes sense.
Thank you!!! Excellent tutorial. I will definitely be back for more from Chester :)
What is 38 ?
Hy! Awesome video, might apply it in the future, but there is one thing. It looks like it does not work in case of conditional formatting. Is it any way to do it? Should I maybe just use a different attribution number instead of 38? Thanks in advance!
I'm wondering this same thing
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"?
you are a genius !!! thank youuuuuuuu
Thanks brother
So useful
Thank you! I have been looking for the other index numbers to extract other text - mentioned in your tutorial. Can you help please?
Awesome and again I agree that you can't update automatically if you change the color on a cell. Let me know if there is other way to do it.
Try bypassing this and going straight to VBA.
It does if you colour with format painter, although not helpful if you are not using the same colour over and over again.
Amazing sir...
Very helpful. Thank you
Can we make it automatically by vba? I tried by vba function target.calculate, but it doesn't work at all. I used to have meeting, and some cells coloured with green for complete parts to assembly, yellow for partial complete, and red for not complete at all. When I was asked how many red, yellow and green, I use that formula, but if some cells color changed. That function need to be recalculate. Thank you very much for your paying attention
Hi! Is there a way to do the same on Google Sheets? Thank you!
thanks
Help please. What is the index numbers to extract other text - you mentioned in your tutorial.
thank you thank you thank you
does it work the same with row data?
Is it possible to change the background color of a shape, based on the value in another cell? For example, let's say I have a cell with a percentage, and I want the background color of a shape to change based on percentage ranges within that cell. So, if the percentage is 30% or less, the background of the shape would turn to red. If it was between 60% and 31%, it would change to yellow. And then if the cell contained a percentage of 61% or above, it would be green. Is it possible to do this without using VBA?
Thanks sir 👍
It's to very helpful videos
Thanks, is there a way to count by font colour?
It's not working for me... is it because the cells I'm wanting it to sum have background determined by a conditional formatting? How can I still use this if the cell has a conditional format?
already found solution dude? i'm facing same problem with you
why don't the values in the total sum cell change on real time if input amount was changed
The fact that it doesn't apply to conditional formatting is really a deal breaker.
YEAH it sure is! I just spent ages thinking it was something I did wrong!
@@andrewwest5344 Same dude
I just spent an hour trying to get this to work. If only I had read your comment first!!!
Does it work for conditional formatting colour?
Thanks, super useful
Beautiful. Thanks bro
Chubak Miaouw yes its wonderfull... but i want the figure change when i change the colour, how to do that...don’t want to keep on copy and paste the formula..
I was able to get this formula working but I want to be able to change numbers in the coloured cells but I get a #NAME? Error. How do I fix this?
hi is there a work around to automatically update the color numbers when the color is changed?
I have looked everything in google and haven't found an easy way, and I would like to have one, how Microsoft hasn't thought of this? I have web excel that I would like to count the colors cell as we add and remove names and backgrounds but haven't found a way yet... this was helpful but still cant help me :(
What do you know about the function Cell()? That is supposed to give a number for Cell("color",D4). I haven't figured out yet although Cell("Format",D4) does work, as well as most of the other options "Row", "Col", etc.
this number 38 is code for yellow
what of other colour's ?
can't find on the net
Great
I have a row with three different background colors and i need to get the count of the colors in the row. Is that doable ?
THIS IS NOT WORKING FOR ME IM GETTING #NAME? INSTEAD OF A NUMBER
what is 38 & why result is 6
Your answer is here, excel color index
why I'm getting #name error?
It's not working for my MS office 360. The name manager formula is not recognisable by the system.
how did you get the value of argument 38?
That I38 was confusing, rest was great
Why does my Excel program just shut itself off the moment I type in or choose =CellColour like at 1:50? Even if I have more than 2 tabs of Excel open, it just immediately closes the programs.
Super interesting Method. Didn't know about this! Thanks for the explanation Chester! Do you have a website by any chance?
bluepecantraining.com
@@ChesterTugwell hey there, can I send a sheet to check my errors? I can get the cell colour number sorted but can't get the countif formula to work?
It is better I count the colors myself then use such high level rocket science stuff!!!!!!
I did the sum CellColour formula & it worked fine. I then linked another spreadsheet to the totals. Now when I open the spreadsheet with the CellColour formulas in it & add more lines to the data, I get a #BLOCKED message coming up when refreshing the cells. Why is this? I have a registered version of Office365 & am connected online. I removed the links & still get the #BLOCKED error message when I try & update new cells.
I can't get cell colour .😓😓
Does "get.cell" still exist? I tried it with "cell" but it doesnt work..
why name manager disappeared when sending out the saved file?
not working it shows me number 0 instead how to fix this ? i did try all your step
Do you have the formula for the font colour?
The only way you could have the colors updated if you save a color keys on the sheet. For example, list all of the colors you will be using, then copy the color you will need from the list every time instead of using the menu bar.
hello for me not working, getting error that it is a formula and excel want to count something
im trying to use the get.cell function however i am getting an error of #NAME can you please help
Even I'm getting the same error. Any solution on this?
Suuupeeeerrr!!! I remember that I spent a lot of time looking for a way of how doing this, at last I had to use vba and even if I couldn't get what I expected but this is awesome.
One thing, I didn't understand very well why using number 38 in the function?
38 is used to get the color of the cells. The function is used to get various informations of a cell and 38 is to get the color. Other numbers will get other informations. I hope this helps
@@yoda7105 Super!!! Thanks
@@yoda7105 So 38 will always work to get color?
@@hrideyesh9139 unless Microsoft changes it
same I have tried but its not calculating
My excel doesn't have the Name Manager function at all, this can't be done without it I'm assuming "sigh"
I don't know if that's a dumb question, but what if I want to count using the font color not the background?
Try th-cam.com/video/XFpO_q1HJV8/w-d-xo.html
It didn't work for me. I get #NAME? error
Can this be done in numbers?
How did you get the 38?
what does 38 represent?
Nice :)
What is 38 you will put on default can you plz explain, concept cant understand that point.
He stated that 38 is a type of pull reference particularly, color. any other number must be a pull reference of font, icon, etc imo.
Hi how to vlookup cell color value in excel
Where did 38 come from!?
may be you can explain what the 38 is??
With Get.Cell you can retrieve data about a cell including background colour. The 38 is the index number for the cell's colour. Other numbers retrieve other info about the cell. Microsoft documentation on this is sparce.
Is the number 18?
Question for you.. first of all thanks for the trip. Wondering if it is possible to assign a color a value? That is to sat when I highlight a cell that I can associate that color with a value always equaling 10 as an example.. best, Edward
In the conditional formatting.. highlight cells....text that contains...10..... then instead of "light red fill with dark red text" click the drop down choose custom format... press okay.... choose tab fill.... choose the color...choose ok.... then okay
Why we can use only 38 in that formula why not other number, please explain me ??
The 38 is the index number for the cell's colour
@@pawanbhardwaj848 so where i can see the number of the color i want ?
why 38?
From where you get this get color I can't find it Chester.Plz make it clear
Just google ‘excel color index’
CellColour ????????????????
Where did this came from?
"No VBA Required" // Follows exact instructions, Excel: "VBA required to resume"
What the hell with this damned excel! If I want to write 38 there, the excel report me an error and I cant continue....
Im having the same problem
My =get.cell formula didn't work, "There is a problem with this formula" notice popped up.
saved my workbook as macro-enabled .xlsm, yet no help