-1

I have the below open reservation SAP report that has reservations corresponding to materials and the total available stock in warehouse. How do i add the total quantity required for a particular material no.(sum of all reservations), compare with the available stock and highlight the materials that are less than available stock?

Total requriement qty. of material 71958 is 13 compared to the available stock of 12. I need this material to be red flagged.

Have around 10000 materials to be compared in the sheet.

Appreciate your help.

Thank you.

enter image description here

  • 1
    Do you want the whole row highlighted red or just the cell? – Forward Ed Aug 9 at 16:20
0

lets assume Material is column A and row 1. Lets start by making a formula that will do the check and then we can apply that formula to conditional formatting which will take care of the highlighting aspects

In this case I would look at the SUMIF() function. It take the following format:

SUMIF(RANGE OF CELLS TO CHECK, WHAT THE CHECK IS, RANGE OF CELLS TO SUM)

So in your case the formula would look something like:

=SUMIF($A:$A,$A2,$C:$C)

That will get you the total number of material A2 and so on as its copied down. What you need to do now is compare that to the amount in stock for a true false condition. in this case you want to trigger when stock is less than the required amount. You could set up your formula as follows:

=SUMIF($A:$A,$A2,$C:$C)>$D2

That should evaluate to TRUE.

Select the range of cell you want to apply this to and make sure the top left cell is the active cell of the selection set. Go to the Home Ribbon and head on over to the Style portion and select conditional formatting;

Conditional Formatting

From the menu that will pop up select "New Rule" and enter your formula in the box in the middle. Click the format button and select all the formatting you want to apply when the formula evaluates to TRUE.

Formatting Rule

From the window that comes up select "Use a formula..."

UPDATE: What The Check?

What the check is, is a boolean expression. It will normally be one of =, <, >, >=, <= or <>. Now by default if no comparison string is added it is treated as = by default. In order to add a comparison you tend to do something like ">"&cell reference. You formula is actually:

=SUMIF($A:$A,"="&$A2,$C:$C)>$D2
  • Thanks a lot Ed. It worked. Can you please explain the syntax of the sumif function? Especially "what the check is" or criteria. Thanks again and appreciate your help. – Shiv Aug 11 at 11:28
  • @Shiv Added explanation. Let me know if it suffices. – Forward Ed Aug 12 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.