I have a forecast spreadsheet with columns listing potential incoming sales for the month. I want to be able to click on a cell, change it's color to green and have it be included in the total below. I have looked at the thread on this website on this subject but it is of no help (mainly because I don't understand it). If someone could offer me a very basic yet detailed way to do this, I would be most grateful. Thanks very much and Happy Holidays.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel 97: sum cell based on color 1
- Thread starter Maine
- Start date
-
1
- #2
DaleWatson123321
Programmer
Maine,
I've created a working example for you, which I will describe below, but you might be better off to send me an email, and I'll return the file.
The sum formula is as follows:
=SUMIF(B16:B22,"=1",A16:A22)
It works such that if you enter the value "1" in column "B", the "Conditional Formatting" in column "A" will cause the color in the adjacent cell in column "A" to change to GREEN. And, the SUM formula will THEN add the value in column "A".
The "condition" of the "Conditional Formatting" is a formula which refers to the following formula in column C:
=A16+B16-1
You could narrow the width of column "B" and also format the entire column to "White text" so that the "1" value will not show. The formulas in column "C" could be hidden
by hiding the entire column.
As mentioned, the sample file is yours for the asking. All I need is for you to email me, and I'll return the file.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
I've created a working example for you, which I will describe below, but you might be better off to send me an email, and I'll return the file.
The sum formula is as follows:
=SUMIF(B16:B22,"=1",A16:A22)
It works such that if you enter the value "1" in column "B", the "Conditional Formatting" in column "A" will cause the color in the adjacent cell in column "A" to change to GREEN. And, the SUM formula will THEN add the value in column "A".
The "condition" of the "Conditional Formatting" is a formula which refers to the following formula in column C:
=A16+B16-1
You could narrow the width of column "B" and also format the entire column to "White text" so that the "1" value will not show. The formulas in column "C" could be hidden
by hiding the entire column.
As mentioned, the sample file is yours for the asking. All I need is for you to email me, and I'll return the file.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
DaleWatson123321
Programmer
Maine,
I see you're logged in today, and I'm curious to know if you noticed my response to your request for a solution.
As I have the file ready to send, it would be a shame for you not to receive it, as it will make your understanding of the solution MUCH easier.
Just email me and I'll return the file.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
I see you're logged in today, and I'm curious to know if you noticed my response to your request for a solution.
As I have the file ready to send, it would be a shame for you not to receive it, as it will make your understanding of the solution MUCH easier.
Just email me and I'll return the file.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
harrisharris
MIS
This was of much interest to me - thnaks Dale for emailing your solution - much appreciated.
- Status
Similar threads
