Cell Highlighted with color

[Vino]

Hi,

Good Afternoon,

We have date wise requirement of our products. Suppose Finished goods available stock is 1000, the available stock will sufficient for how many days ( the cell should highlighted by some color automatically)


Example sheet is attached.

Pls give the solution to this.

VVK

[pieterse]

Have a look at Conditional formatting (Format, Conditional format).

[Vino]

Hi pieterse


In conditional formating we knew the color changing option.

We need based on available stock the cells should be high lighted.

Suppose the requiremet is 2000and available stock is 1000 it will come upto 10 days those cells only high lighted

VVK

[pieterse]

CF allows you to do such things. See example.
Change cell A2 to may first and cell B2 will turn red. Same goes for changing the # of days to a value of -say- 40 days.

[Vino]

Hi pieterse.

Can you help on this.

In Uploaded format. In that 3 products are there, the day wise requirement of each product is entered. In last cell as on date requirement total is summed, if the date changes the summed qty will change automatically, without manual intervention.

Product – 1 as on date requirement (20th may) is 614nos and available stock is 634nos. cells which are inline with 634nos should highlighted in green.

Product – 2 as on date requirement (20th may) is 327nos and available stock is 307nos. there is shortage of 20nos for 20th requirement. Cells which are inline with 307nos should highlight in green and cell which have shortage the cell should highlighted in red.

Product – 3 as on date requirement (20th may) is 101nos and available stock is 98nos. there is shortage of 3nos for 20th requirement. 20th actual requirement is 6Nos out of 6Nos we have 3Nos in stock. Cells which are inline with 95nos should highlight in green and 20th is having shortage of 3Nos it should highlight in yellow.


If we did the report we can save around 1Hrs/day.


VVK

[HansV]

See the attached version.

Look at Format | Conditional Formatting to see how it's done.

[pieterse]

SHould have looked further down the message list.

I built the exact same set of CF rules as you did Hans, then wanted to post, only to discover your file.

[Vino]

Hi Hans

Excellent Job.

Thank you very much.

The day wise requirement of each product is entered. In last cell as on date requirement total is summed, if the date changes the summed qty will change automatically, without manual intervention.

Example:

Product-1 upto 20th total is 614nos and 21st qty is 20Nos. Summed total should change automatically if the system date changes

[HansV]

You provide a statement, not a question. Is this a quote from a textbook or homework excercise?

[Vino]

Hi Hans,

Good Morning,

Its a question.

We are adding the requirements from month begining to as on date, to know the cummulative requirements

Can you help on this, Daily we are adding the particular day cell. To avoid that can you give suggest best.VV

vvk

[HansV]

You can use SUMIF for that. In the attached version, I added the dates for the rest of the month, so that the dates are in B2:AF2.
The formula for the total in row 3 up to and including today is

=SUMIF($B$2:$AF$2,"<="&TODAY(),$B3:$AF3)

[Vino]

Hi Hans

Good Morning

Thank you very much.

VVK