Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Connie Martin
 
Posts: n/a
Default 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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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   Report Post  
Connie Martin
 
Posts: n/a
Default

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   Report Post  
Mark B via OfficeKB.com
 
Posts: n/a
Default


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   Report Post  
Connie Martin
 
Posts: n/a
Default

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   Report Post  
Connie Martin
 
Posts: n/a
Default

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   Report Post  
Mark B via OfficeKB.com
 
Posts: n/a
Default


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   Report Post  
Connie Martin
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
cell color index comparison MINAL ZUNKE New Users to Excel 1 June 30th 05 07:11 AM
Cell Change Color - Need Help alani New Users to Excel 3 June 29th 05 03:50 PM
Pasting Word table cell with paragraph markers into single Excel c Steve Excel Discussion (Misc queries) 1 June 16th 05 11:26 PM
inserting data from a row to a cell, when the row number is specified by a formula in a cell [email protected] New Users to Excel 2 January 6th 05 08:18 AM
VLookup resulting in a blank cell... KempensBoerke Excel Worksheet Functions 1 October 28th 04 09:57 PM


All times are GMT +1. The time now is 06:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"