remaining quantity bar (related to total quantity)

I've never found a way to make Data Bars work w relative references. I think you'd have to define CF for each row individually...YUCK. Maybe someone else knows a work-around. Apologies if you've already thought of this, but the only thing I can offer is a couple of ways to show progress in a separate column. Here's a picture:

InStock2.png

Method#1 employs repeated pipe characters ("|") generated with a formula:=IFERROR(LEFT(REPT("|",23),23*M6/K6),""). Pretty basic.
Method#2 uses Data Bars in the center column set to reflect the underlying percentages yielded by this formula: IFERROR(M6/K6,"").
You'll need to set the Min Value to 0, the Max Value to 1, and check the Show Bar Only box in the CF dialog box.

give the attached a trial - put your remaining qty in 'M6' downwards - green bar should be proportional to qty - red bar if error.
Macro on Worksheet_Change event.

torachan: thank you but i would prefer to avvoid macro.

leelnich: thank you. a quesion: the formula recall cell L4:

what is value in the cell L4? shouldn't i use only cell K6 and M6 (Q.ty total and remaining q.ty)?

Maybe i should use =LEFT(REPT("|",M6),M6*M6/K6)

but what is M6*M6/K6?


maybe is have to use
=LEFT(REPT("|",K6),M6) ?

Originally Posted by vermaccio

...thank you but i would prefer to avoid macro.

I've been working doubles for a couple of days, just got back on this last night. I see torachan beat me to it, and vermaccio's not interested, but here's my VBA code. This has 2 extra features; the bar responds to changes in EITHER column, and it handles multiple pasted entries.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MCL As Range, KCL As Range

With Range(Cells(6, "K"), Cells(Rows.Count, "K").End(xlUp))
    Set Target = Intersect(Target, .Offset(0, 2))
End With

If Not Target Is Nothing Then

For Each MCL In Target
    Set KCL = MCL.Offset(0, -2)
    With MCL.FormatConditions
        .Delete
    With .AddDatabar
        .MinPoint.Modify xlConditionValueNumber, 0
        .MaxPoint.Modify xlConditionValueFormula, "=" & KCL.Address
        .BarFillType = xlDataBarFillSolid
        
        If KCL > 0 Then
            .BarColor.Color = RGB(255, 85, 90)
        End If
        If KCL < MCL Then
            .BarColor.Color = RGB(255, 0, 0)
        End If
    End With
    End With
Next

End If

End Sub

Originally Posted by vermaccio

leelnich:... the formula recall cell L4:

what is value in the cell L4? shouldn't i use only cell K6 and M6 (Q.ty total and remaining q.ty)?...

maybe is have to use
=LEFT(REPT("|",K6),M6) ?

Not sure what you're referring to. I might have missed some worksheet "cleanup" after de-bugging. Sorry if that caused confusion.

Referencing THIS formula: =LEFT(REPT("|",23),23*M6/K6) , which appears in L6 and is copied down, by trial and error I found it took 23 pipes to stretch all the way across the cell. You may need to adjust that; use whatever fits. The important part is the ratio defined by M6/K6; it determines how many pipes (out of 23) are displayed.

I could have written it like this: =REPT("|",23*M6/K6), but that would return too many pipes for the cell if M6 inadvertently exceeds K6, which would alter your row height if you have selected Wrap Text for that cell.

If you want the representative quantity and the data bar in the same cell - @leelnich has already identified in post#2 that you will need to conditionally format each individual cell - as this involves a calculation with the inputs of columns 'K' & 'M' this would cause a 'circular reference' and fail - you are left with a choice - databar in separate cell or use VBA - unless I am mistaken and someone else knows something that I have missed.

Another way (in an adjacent cell):

=I6/G6

Once you select "show bar only" and OK it, the values vanish and only the bars remain.

I think Lee already suggested that, Glenn (see line #3 of post #2). I think it's the best compromise.

I have never undersood why MS has failed to make data bars etc. capable of using relative references.

So he did... I stopped reading after the =LEFT(REPT("|",23),23*M6/K6) bit.

LOL!

Been there and got the T-shirt ...

thanks for the feedback and added rep point - glad to have helped.