Changing color of a cell in a table...
Solved/Closed
Related:
- You can give different colors to each cell in a table
- School time table software free download full version - Download - Organisation and teamwork
- Rj45 colors - Guide
- Html table size to fit window - Guide
- Which key is used to make multiple line in a single cell - Guide
- How to delete a row in a table in word - Guide
4 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 21, 2010 at 10:08 AM
Feb 21, 2010 at 10:08 AM
Could you possibly put your file at some share site like https://authentification.site
Some feedback:
Well I have tested it using the maximum possible number of calculations for one day (224), there was no delay in all of the colors changing so I would say that this method of doing what I wanted to do is perfect.
Using something like the same formula I have also created a sheet which counts the number of dispatch slots that are used each day, to analyse how full my trucks should be when they leave my site. It's all very useful.
However, I was toying with how to count the products by the type of packaging that was used and that failed.
My newest idea here is to use a drop down menu to select the type of packaging into a cell on the product matrix sheet and then getting a cell on another sheet to count the number of times that each type occurs on a date. So far I have the ability to count the number, that's the easy part. But I just can't get it to give me the number for the defined date.
Bottom line, it's all good and very useful. I'm just hoping that MS Office10 won't change from this too much, as I have learned so much and don't want it to become useless information when I upgrade!
Well I have tested it using the maximum possible number of calculations for one day (224), there was no delay in all of the colors changing so I would say that this method of doing what I wanted to do is perfect.
Using something like the same formula I have also created a sheet which counts the number of dispatch slots that are used each day, to analyse how full my trucks should be when they leave my site. It's all very useful.
However, I was toying with how to count the products by the type of packaging that was used and that failed.
My newest idea here is to use a drop down menu to select the type of packaging into a cell on the product matrix sheet and then getting a cell on another sheet to count the number of times that each type occurs on a date. So far I have the ability to count the number, that's the easy part. But I just can't get it to give me the number for the defined date.
Bottom line, it's all good and very useful. I'm just hoping that MS Office10 won't change from this too much, as I have learned so much and don't want it to become useless information when I upgrade!
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 28, 2010 at 08:36 AM
Feb 28, 2010 at 08:36 AM
not exactly sure what you would like to do. On the surface it seem that COUNTIF should do the work for you.
"countif" is what I'm playing with here. In English, I am trying to tell the spreadsheet:
"Count all of the times when a dispatched product is packaged in × on the specified date"
In practice, this is hard to do! I have been able to get it to count how many times the packaging type is used - but that is information I already know, what I need to do is find a way to get it to look at the "d", and then identify the packaging type and return the number of occurrences for the specified date. Since there are 4 packaging types, i would be able to see not only the total number of products to be dispatched on a date, but also which packaging I am using and therefore, look at my costs and dispatch policy in more detail. The ability to forecast this would be useful in many ways but mostly, I am eager to learn more about formulas and see what they can do for me.
"Count all of the times when a dispatched product is packaged in × on the specified date"
In practice, this is hard to do! I have been able to get it to count how many times the packaging type is used - but that is information I already know, what I need to do is find a way to get it to look at the "d", and then identify the packaging type and return the number of occurrences for the specified date. Since there are 4 packaging types, i would be able to see not only the total number of products to be dispatched on a date, but also which packaging I am using and therefore, look at my costs and dispatch policy in more detail. The ability to forecast this would be useful in many ways but mostly, I am eager to learn more about formulas and see what they can do for me.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 28, 2010 at 08:48 AM
Feb 28, 2010 at 08:48 AM
A picture is better than a thousand words. may be put a file up.
Also, a suggestion may be open a new thread on this, so that if down the road some one else is seeking same sort of issue, it just might help him.
Also, a suggestion may be open a new thread on this, so that if down the road some one else is seeking same sort of issue, it just might help him.
https://ccm.net/forum/affich-275706-countif-based-on-two-criteria
I started a new thread for the new project
I started a new thread for the new project
Feb 21, 2010 at 10:36 AM
I have hosted an example of what i work with. it's an Office 2000 creation but I will be using office 2003 the the finished article.
What i would like to do is have the macro search for the date mentioned in sheet 2, D4 and then apply colours to the product ID's in the table, the product ID's are named in sheet1 so i imagine that the code would look at that name and then match it up with the identical name in the table, sheet2.
I have decided to focus on just one criteria for the macro, the "D" value. This is because i imagine any code would simply be repeated for any other value, and also because it is by far the one that i use the most (for obvious reasons).
I must mention that the product codes in real life are different and much more numerous, as are the customers (you can imagine a need not to reveal any sensitive information)
Thank you for a quick response! I was suprised to say the least
Feb 22, 2010 at 09:30 AM
First to recap what you had.
1. Two sheets (sheet 1 and sheet 2)
2. Sheet 1 contained dates in column B starting at row 5
3. Sheet 1 contained product names in row 4, starting from column C
4. at the intersection of various dates and productions, you had codes like "d", "s" etc
5. Sheet 2 contained a date value in cell d4 against which you wanted to do a check up for products
6. Sheet 2 contained a multi-column table starting at d8. The header of the table had various customers and underneath each customer you had products
7. You wanted to changed the color of the product under each customer if the in sheet 1, for that date and product, there is a certain code.
Now this is how I approached it. I tried to stay away from macro. These are the steps
1. Name the range where the dates are as "DATES" ( in your sample, that would be sheet1, column B). Start at the first row (B1) (even though your data started at row 5)
2. Name the range where the products are as "PRODUCTS" ( in your sample, that would be sheet1, row 4). Start at the column A (even though your data started at column C)
3. go to to sheet 2 and Select all columns that had company names (in your sample that would start at D till G)
4 Go to conditional formatting and apply this formula
=INDIRECT(ADDRESS(MATCH($D$4,DATES,0),MATCH(D1,PRODUCTS,0),1,TRUE,"sheet1"))="d"
5. go to format and change the color to green
6 Go to conditional formatting and apply this formula
=INDIRECT(ADDRESS(MATCH($D$4,DATES,0),MATCH(D1,PRODUCTS,0),1,TRUE,"sheet1"))="s"
7. go to format and change the color to red
You can add similarly other format conditions
Here is the sample file with formatting.
https://authentification.site/files/21075943/CondtionalFormatXREF.xls
Feb 22, 2010 at 12:50 PM
I always knew that there was a way to do this sort of thing. Thank you!
Feb 22, 2010 at 01:57 PM