Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IF the cell is yellow...
I have a huge spreadsheet of data. Certain cells are coloured yellow. What
I would like is a formula that I can enter in all the cells of the column that would say: "If this particular cell is coloured yellow then it should equal O43, otherwise it's to be left blank." Thank you Connie |
#2
|
|||
|
|||
Did you color them via Format|Cells or Format|Conditional formatting.
If the first, then visit Chip Pearson's site and see how you can use a UserDefined function return the color of a cell. http://www.cpearson.com/excel/colors.htm If the color of the cell is from format|conditional formatting, I think I'd just use the same rules in another cell (a nice formula) to determine what color the cell should be. But if you want to try some VBA (not for the faint of heart), Chip has some code: http://www.cpearson.com/excel/CFColors.htm Connie Martin wrote: I have a huge spreadsheet of data. Certain cells are coloured yellow. What I would like is a formula that I can enter in all the cells of the column that would say: "If this particular cell is coloured yellow then it should equal O43, otherwise it's to be left blank." Thank you Connie -- Dave Peterson |
#3
|
|||
|
|||
I'm just getting back to this worksheet today and came here to see if anyone
answered. I don't think this is what I'm looking for, at least I don't see anything there that answers my question. Also, it is somewhat Greek to me. All I want is that as soon as I colour the cell yellow, it will put in the value of another cell. I have a feeling this cannot be done. Connie "Dave Peterson" wrote: Did you color them via Format|Cells or Format|Conditional formatting. If the first, then visit Chip Pearson's site and see how you can use a UserDefined function return the color of a cell. http://www.cpearson.com/excel/colors.htm If the color of the cell is from format|conditional formatting, I think I'd just use the same rules in another cell (a nice formula) to determine what color the cell should be. But if you want to try some VBA (not for the faint of heart), Chip has some code: http://www.cpearson.com/excel/CFColors.htm Connie Martin wrote: I have a huge spreadsheet of data. Certain cells are coloured yellow. What I would like is a formula that I can enter in all the cells of the column that would say: "If this particular cell is coloured yellow then it should equal O43, otherwise it's to be left blank." Thank you Connie -- Dave Peterson |
#4
|
|||
|
|||
I think that the first question that needs to be answered is: Why are the cells colored yellow? Are they colored yellow because of their location or because they represent a subtotal or some other reason? Once that is answered it may be easier to proceed. While it appears possible to make the value of a cell equal something else based upon it's color, I believe it is an advanced method. We may be able to solve your problem in a simpler manner. Connie Martin wrote: I have a huge spreadsheet of data. Certain cells are coloured yellow. What I would like is a formula that I can enter in all the cells of the column that would say: "If this particular cell is coloured yellow then it should equal O43, otherwise it's to be left blank." Thank you Connie -- Mark Brindamour Message posted via http://www.officekb.com |
#5
|
|||
|
|||
I have a huge list of arrival dates of transport trucks into Receiving (Col.
M in the sample below). Each truck has skids and skids of orders, which are all listed row by row (thus, you see a repeat of the arrival date because each row is a different order, but on the same truck). I have columns calculating the transit time, one which includes weekends & holidays (COL. O), and another excluding these days (COL. P). In column R and S I want to show only once the transit time of columns O and P on the first row of the arrival of that truck (there is another formula in the worksheet based on the numbers in columns R and S, which value would be greatly distorted if these numbers repeated for every row, therefore I cannot use the transit time numbers in columns O and P). For a particular reason, I colour the cells in columns R and S yellow which contain the values from O and P, which I put in manually. However, I would like to simply colour the cells yellow and they would know then to take the values from Col. O and P and plug them in, without me having to also type them in manually. But if this is a complicated affair, doing it manually may be the way to go. Scroll down..... COL. L COL. M COL. N COL. O COL. P COL. Q COL. R COL. S Jul-06-05 Jul-11-05 (5) 5 3 Trans X 5 3 Jul-20-05 Jul-11-05 9 5 3 Trans X Jul-20-05 Jul-11-05 9 5 3 Trans X Jul-20-05 Jul-11-05 9 5 3 Trans X Jul-19-05 Jul-11-05 8 5 3 Trans X Jul-19-05 Jul-11-05 8 5 3 Trans X Jul-19-05 Jul-11-05 8 5 3 Trans X Jul-19-05 Jul-11-05 8 5 3 Trans X Jul-19-05 Jul-11-05 8 5 3 Trans X Jul-08-05 Jul-11-05 (3) 5 3 Trans X Jul-15-05 Jul-11-05 4 5 3 Trans X Jul-15-05 Jul-11-05 4 5 3 Trans X Jul-14-05 Jul-11-05 3 5 3 Trans X Jul-14-05 Jul-11-05 3 5 3 Trans X Jul-06-05 Jul-11-05 (5) 5 3 Trans X Jul-13-05 Jul-11-05 2 5 3 Trans X Jul-14-05 Jul-12-05 2 5 3 Trans X 5 3 Jul-21-05 Jul-12-05 9 5 3 Trans X Jul-20-05 Jul-12-05 8 5 3 Trans X Jul-20-05 Jul-12-05 8 5 3 Trans X Jun-20-05 Jul-12-05 (22) 5 3 Trans X Jul-19-05 Jul-12-05 7 5 3 Trans X Jul-19-05 Jul-12-05 7 5 3 Trans X Jul-19-05 Jul-12-05 7 5 3 Trans X Jul-19-05 Jul-12-05 7 5 3 Trans X Jul-19-05 Jul-12-05 7 5 3 Trans X Jul-08-05 Jul-12-05 (4) 5 3 Trans X Jul-14-05 Jul-12-05 2 5 3 Trans X Jul-25-05 Jul-15-05 10 3 3 Trans X 3 3 Jul-28-05 Jul-15-05 13 3 3 Trans X Jul-21-05 Jul-15-05 6 3 3 Trans X Jul-27-05 Jul-15-05 12 3 3 Trans X Jul-27-05 Jul-15-05 12 3 3 Trans X Jul-26-05 Jul-15-05 11 3 3 Trans X Jul-26-05 Jul-15-05 11 3 3 Trans X Jul-26-05 Jul-15-05 11 3 3 Trans X Jul-19-05 Jul-15-05 4 3 3 Trans X Jul-18-05 Jul-15-05 3 3 3 Trans X Jul-15-05 Jul-15-05 0 3 3 Trans X Jul-27-05 Jul-19-05 8 5 3 Trans X 5 3 Aug-02-05 Jul-19-05 14 5 3 Trans X Aug-02-05 Jul-19-05 14 5 3 Trans X Aug-02-05 Jul-19-05 14 5 3 Trans X Jul-29-05 Jul-19-05 10 5 3 Trans X Jul-28-05 Jul-19-05 9 5 3 Trans X Jul-21-05 Jul-19-05 2 5 3 Trans X Jul-28-05 Jul-19-05 9 5 3 Trans X Jul-28-05 Jul-19-05 9 5 3 Trans X Jul-28-05 Jul-19-05 9 5 3 Trans X Jul-28-05 Jul-19-05 9 5 3 Trans X Jul-28-05 Jul-19-05 9 5 3 Trans X Jul-28-05 Jul-19-05 9 5 3 Trans X Jul-19-05 Jul-19-05 0 5 3 Trans X "Mark B via OfficeKB.com" wrote: I think that the first question that needs to be answered is: Why are the cells colored yellow? Are they colored yellow because of their location or because they represent a subtotal or some other reason? Once that is answered it may be easier to proceed. While it appears possible to make the value of a cell equal something else based upon it's color, I believe it is an advanced method. We may be able to solve your problem in a simpler manner. Connie Martin wrote: I have a huge spreadsheet of data. Certain cells are coloured yellow. What I would like is a formula that I can enter in all the cells of the column that would say: "If this particular cell is coloured yellow then it should equal O43, otherwise it's to be left blank." Thank you Connie -- Mark Brindamour Message posted via http://www.officekb.com |
#6
|
|||
|
|||
I just created a macro and put it on the menu bar. When I'm in the cell I
want to be in and click on it it completes the two cells perfectly----colours them and puts in the correct values. Maybe this is the way to go. Connie "Mark B via OfficeKB.com" wrote: I think that the first question that needs to be answered is: Why are the cells colored yellow? Are they colored yellow because of their location or because they represent a subtotal or some other reason? Once that is answered it may be easier to proceed. While it appears possible to make the value of a cell equal something else based upon it's color, I believe it is an advanced method. We may be able to solve your problem in a simpler manner. Connie Martin wrote: I have a huge spreadsheet of data. Certain cells are coloured yellow. What I would like is a formula that I can enter in all the cells of the column that would say: "If this particular cell is coloured yellow then it should equal O43, otherwise it's to be left blank." Thank you Connie -- Mark Brindamour Message posted via http://www.officekb.com |
#7
|
|||
|
|||
well glad you found something that worked for you as I am a little unsure if I understand what you are trying to do- no matter it seems like you understand it perfectly which is wha's important. Connie Martin wrote: I just created a macro and put it on the menu bar. When I'm in the cell I want to be in and click on it it completes the two cells perfectly----colours them and puts in the correct values. Maybe this is the way to go. Connie I think that the first question that needs to be answered is: Why are the cells colored yellow? Are they colored yellow because of their location or [quoted text clipped - 10 lines] Thank you Connie -- Mark Brindamour Message posted via http://www.officekb.com |
#8
|
|||
|
|||
Thank you, just the same, for endeavouring to help me in my muddle. It is
much appreciate. You shall see me again, no doubt!! Connie "Mark B via OfficeKB.com" wrote: well glad you found something that worked for you as I am a little unsure if I understand what you are trying to do- no matter it seems like you understand it perfectly which is wha's important. Connie Martin wrote: I just created a macro and put it on the menu bar. When I'm in the cell I want to be in and click on it it completes the two cells perfectly----colours them and puts in the correct values. Maybe this is the way to go. Connie I think that the first question that needs to be answered is: Why are the cells colored yellow? Are they colored yellow because of their location or [quoted text clipped - 10 lines] Thank you Connie -- Mark Brindamour Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell color index comparison | New Users to Excel | |||
Cell Change Color - Need Help | New Users to Excel | |||
Pasting Word table cell with paragraph markers into single Excel c | Excel Discussion (Misc queries) | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |