Conditional Formatting

[Pisasu69]

Hello!

This would be my 1st post, so if I did anything wrong pls guide.

Referring to the excel sheet, Overview of the excel sheet - Inventory activity, Number of product with size mix, Sales orders from customer, Total Stock, Stock In and Stock Out ( based on the orders ).

Forum_Formatting.xlsx

I want to further enhance the formulation on the table to give a better over view in the order section.

Currently there is conditional formatting on the excel whereby any value inputted on a cell, cell automatically changes to a colour.

1. How can I insert a formula to change the cell colour to green if. Eg CS1 Order Product, Inventory - CS1 Order >=0 and if its negative there will maintain.

2.The same as above but for CS2 Inventory - CS1 orders - CS2 orders >=0. For CS3, its same but now it will be inventory - CS1 order - CS2 order - CS3 order >=0.

Eg - Line 11 Product 12 001 Inventory Total 11, CS1 total 1 (changes to green, 11-1=10), CS3 total 2 (Changes to green, 11-1-2=8), CS5 total 10 ( No Change, 11-1-2-10=-2), CS6 total 6 (No Change, 11-1-2-10-8=-10)

Not sure if this can be done but can we;

3, Using the Eg above, Will be able to accommodate CS1 and CS3 but because the formula follows chronological order CS5 and CS6 will not have insufficient stock to deliver. Is there a formula to calculate that weeks delivery and match the best possible,in this case CS6 changes colour and CS5 no change? I think it might complicate the report but wondering if there is a way to do so.

Extra notes

- All the week delivery is done on a Monday, the inventory tracker would only have 4 dates in months (unless its a 5 week month).
- Once the delivery is done, the whole column will be removed and paste into another area in the same sheet.
- The total inventory is picking up from another cell
- As the orders come, it will be plunked into the relevant week ( usually at the end of the order line, rarely in between current orders so the team just adds a collumn and plucks in the data.)

thanking in advance

If this will make it complicated, please do suggest another method of tracking. Not in a formatting way but some other tracker on a different sheet.

The goal here is to have a quick overview on the deliveries/customer orders that are good to go instead of manual subtracting the inventory with sales by line...Time consuming and tedious. :-)

thank you again. Looking forward for the input.

[xlrocks]

pls check the attached file. Forum_Formatting(1).xlsx
i applied conditional formatting - (1) Green, if sufficient stock (2) Red if no stock
not sure if ur 3rd point is met

And one more thing... in ur last line u wrote

instead of manual subtracting the inventory with sales

when we are using excel, values are automatically updated.
No need of manual subtraction.

[newdoverman]

Maybe this will be of help.

[Pisasu69]

Hey guys, let me play around with couple of scenarios and get back to you...but thanks a lot guys...much appreciated....

[Pisasu69]

Sry guys I am taking some time to revert...been really busy with work lately...

Quick Question - For me to apply the conditional formatting formula on each column, do I have to manual pluck in the formula by column? I only can drag down the condition right?

[FlameRetired]

Have you tried Format Painter? It's very handy for this sort of thing.

[xlrocks]

I could not understand ur question completly.

But still i will reply to what i understood.

1st select the column in which conditional formating is already their.

then Conditional Formating >> Manage Rules

in window (or BOX) Edit "applies to"

pls see the attached image
ScreenHunter_54 Oct. 15 20.24.jpg

[xlrocks]

dont know why image is not getting attached

[newdoverman]

This is a test picture using the Paperclip for file attachments.

It uploaded properly so I deleted it.

How are you attaching the picture file?

[Pisasu69]

Hey Guys

Once again, apologies for the delay in my respond especially since I am the one asking for the help :-)

I get how the Conditional Formatting works, and how i can copy the formatting. thanks xlrocks and FlameRetired.

The methods and formula was clear and it gets what I want done..I am using newdoverman formula as it if much simpler but xlrocks your method works like a charm as well....

I tried this formula for the past week and everything went well until the weekly update was done. The problem I encounter ;

Please refer to pic, sheet ''Before Shipment' the conditional formatting is applied to Column L(order3),M(order4), and N(order5). On a weekly basis, after the stock count and updates are done. The shipment which are shipped out will be moved to another column in the same excel sheet.(Between column D to G)

Please refer to pic After shipment sheet - Order 3 was moved to Column G. This of course will affect the formatting on Column M and N.

How can i maintain the conditional formatting as how it was before shipment even after moving order 3?

BeforeShipment.pngAfterShipment.png

[newdoverman]

Your pictures are not available to view.

[Pisasu69]

I did upload them and I can view them on the post..previously I could upload Xl files but now I can find the way to do that...any particular reason? Can I PM you the files?

[newdoverman]

In case you are not uploading the XLSX file properly, I'll attach the instructions here. There isn't the capability to upload a file in PM.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Pisasu69]

Will do newdoverman...hope the attachment has what is required otherwise please advice what should I add.

I am trying if locking the cell formula will eliminate the issue but so far its not working, either I am not applying it correctly or this is wrong from the Beginning

Forum_Formatting.xlsx

[newdoverman]

Maybe this is what you want. I'm taking a guess by trying to duplicate the 3rd worksheet.

[Pisasu69]

Did you make the changes in after shipment sheet? where the conditional formatting rule is ''cell value more than =0'' becomes green. If yes then sorry that's not the result i am looking at.

The results I am looking for; (referring to the same earlier xl attachment)

1. Before shipment sheet - Lets assume this is my current working sheet. Just to recap;

*Column A:D is the product description
*Column E:F is the shipped out products. Lets call it ''Gone Area" for this explanation :-)
*Column H is the total sales and shipped qty. Its a SUM of the Gone Area
*Column J is the current total stock level in warehouse. The date and stock will be updated after the weekly stock count. This column cell values refers to another column in the same worksheet. For this exercise i remove that portion of the worksheet.
*Column L:R is the orders that have yet to be shipped out. This is where we make the necessary planning to ensure there are sufficient stocks in before the shipment date using the customer order details. Lets call this ''Waiting Area'', As new orders comes in it will be added in to the last column in waiting area.
*Column T is the sum of Waiting Area
*Column U is a formula of Column J - T = x. Basically to plan on ordering to cover the necessary stock.

Assuming this my current worksheet. I have applied 3 conditional formatting to the Waiting Area; (Using cell L11 as the example)

1. L11=0 result format white fill (the rule stops if its true)
2.=AND(J11<>'''',J11-L11>=0) result format green fill
3.Cell value greater than =0 result format red fill

The same rules applies to following column in the Waiting Area only rule 2 will be longer, using N11 as example. the 2nd rule would be =AND(J11<>''''',J11-L11-M11-N11>=0) result format green fill. Everything is awesome up to here.

Once the shipment is out, the order column will removed from the Waiting Area to the Gone Area. This is done by cut and pasted to the last column in the Gone Area. (in this case column L, Order3 is moved column G)

Please refer to the after shipment sheet, Order3 was removed and parked in the Gone Area, once this done the conditional formatting rules applied earlier to the Waiting area gets all messed up cause all the column moved about. How do I maintain the earlier conditional formatting results even if orders from Waiting Area is moved to Gone Area. When the orders are moved, the formula must calculate the same manner. Example of the result looking for;

Rule 2 before shipment sheet, Cell N11 (Order5) =AND(J11<>J11-L11-M11-N11>=0) result format fill green.
Once Order3 is moved.
Rule 2 How it should be sheet, Cell N11 (Order5) =AND(K11<>K11-M11-N11>=0) result format fill green.

I hope you or anyone else in this forum can come up with a solution for this.

[newdoverman]

You can't just switch things around like that and have it work. Conditional formatting is based on "Conditions" and you are totally changing the conditions in the process of changing things around.

[Pisasu69]

Erm...if that's the case what do you suggest to have a similar result...I just need it to show on order level where the stock is insufficient....thanks

[newdoverman]

The enclosed is a proposal for the arrangement of your data. All the inventory data is to the left, separated from the orders and shipments. Row 6 has codes O S. O is for Order, S is for Shipped. No movement of columns is necessary, just change the code in row 6. Calculations can be made to take advantage of these codes.

Freeze Panes has also been applied at H7 so that as data is added, the working area is always in sight both left/right and top/bottom.

All formatting has been removed from the data cells so that you can decide how the calculations are to be made and what you want to have conditionally formatted.

[Pisasu69]

I understand what you are trying to do but unfortunately I cant change the layout of the report. If that is required then I would have to dump this idea for now. thank you again for all the help you have given me, i learned a number of things

[newdoverman]

Thank you for the feedback. Maybe in the future, you will come up with a more workable idea to satisfy your needs.


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Pisasu69]

Hope so I can come out with something...till we meet again