تصحيح معادلة Data Validation

[كمال على طارق]

السلام عليكم أساتذتى الكرام الرجاء من سيادتكم التكرم على مساعدتى بتعديل معادلة Data Validation الموجودة بالعمود E من صفحة الحركات اليومية    

=IF($D3="شراء",$E3,IF(AND(SUMIF(المخزن!$C$3:$C$800,$C3,المخزن!$I$3:$I$800)>=$E3,$D3="بيع"),$E3))

بحيث يمكنى ذلك من ادخال كميات المبيعات لمنتج معين على قدر الرصيد المتاح لهذا المنتج ولا أستطيع إدخال أكثر من المتاح وذلك اذا كانت الحركة بالطبع بيع اما اذا كانت الحركة شراء فبالتالى لا يكون هناك شروط لإدخال أى كميات لهذا المنتج

                                                                                              وشاكر مقدماً على مساعدتكم وبارك الله فيكم

 

مخزن 1.xlsb

[Foksh]

جاري تعديل الملف ليشمل تحديث البيع والشراء وتحديث كمية الرصيد .... إلخ

[Foksh]

ما تم تعديله على الملف :-

1. عمود الوارد H (إجمالي المشتريات) في ورقة المخزن :-

   =SUMIFS('الحركات اليومية'!$E$3:$E$800,'الحركات اليومية'!$B$3:$B$800,$B3,'الحركات اليومية'!$D$3:$D$800,"شراء")

2. عمود الصادر G (إجمالي المبيعات) في ورقة المخزن :-

   =SUMIFS('الحركات اليومية'!$E$3:$E$800,'الحركات اليومية'!$B$3:$B$800,$B3,'الحركات اليومية'!$D$3:$D$800,"بيع")

3. في ورقة الحركات اليومية تم ضبط Data Validation للعمود E ليصبح :-

   =OR($D3="شراء",$E3<=VLOOKUP($B3,المخزن!$B$3:$I$800,8,FALSE))

    

4. أيضاً في الـ Data Validation تم ضبط رسالة الخطأ لتصبح :-
    

 

أيضاً في الخلية C3 في ورقة الحركات اليومية ، تم ضبط الخلية لتجلب اسم الصنف تلقائياً من خلال المعادلة :-

=IFERROR(VLOOKUP(B3,المخزن!$B$3:$C$800,2,FALSE),"")

 

الآن جرب ملفك بإدخال حركة شراء لصنف معين ، وحركة بيع لهذا الصنف ، ولاحظ عملية تحديث الرصيد له في ورقة المخزن . ومن ثم جرب ادخال حركة بيع لكمية أكبر من رصيد الصنف هذا وراقب النتيجة .

 

الملف جربه علي أكون قد وُفقت في تعديلاتي البسيطة 

 

 

مخزن 1.xlsb

تم تعديل منذ 4 ساعات بواسطه Foksh
اضافة المرفق

[كمال على طارق]

شكرا جزيلا لجهود سيادتكم العظيمة , ولكن عندما تم ادخال كمية أكبر من المتاح لكود المنتج رقم 8 فقد قبل معى الإدخال كما ترى بالصورة المرسلة فأصبح الرصيد لهذا المنتج -1 وهذا لا يمكن ان يحدث ..فاذا كان الرصيد المتاح مثلا لأى منتج 7 فأريد ان ادخل أى رقم من 1 : 7     ولكن لا يمكن الإدخال لكمية أكبر من المتاح وهو 7                 ولكم جزيل الشكر

تم تعديل منذ 4 ساعات بواسطه كمال على طارق

[Foksh]

هل ظهرت لك رسالة التنبيه بأن الكمية غير صحيحة ؟؟

 

 

هذه محاولتي .. إلا إذا أردت الذهاب إلى الأكواد والـ VBA 😅

[كمال على طارق]

تظهر الرسالة معى ولكن هذا ليس المطلوب بالضبط ولكنى اريد كما أوضحت سلفاً ان كان رصيد منتج معين هو 7 احتاج عند الإدخال ان اتمكن حتى ادخال 7 للكمية ولا يمكن أدخال اى كمية أعلى وأكثر من 7 لهذا المنتج                    بارك الله فى جهودكم وياريت تكون بدون أكواد VBA ان أمكن ذلك                  .. الا ما اضطررنا لذلك

[Foksh]

منذ ساعه, كمال على طارق said:

تظهر الرسالة معى

طيب تمام ، جرب غير نوع الرسالة الى Stop :-

 

بدلاً من :-

[كمال على طارق]

جربت طبعا ونفس النتيجة .بارك الله فيك

[Foksh]

2 ساعات مضت, كمال على طارق said:

اريد كما أوضحت سلفاً ان كان رصيد منتج معين هو 7 احتاج عند الإدخال ان اتمكن حتى ادخال 7 للكمية ولا يمكن أدخال اى كمية أعلى وأكثر من 7 لهذا المنتج

لم أفهم المشكلة في المطلوب والتنفيذ .. فلو أدخلت قيمة أكبر من المتاح في رصيد الصنف في المخزن ، سيظهر لك رسالة التنبيه وستمنع ادخال الكمية 🤔

إلا إذا كان لك طلب آخر ولم توضحه جيداً أخي الكريم .

 

علك توضح بشكل أكثر تفسيراً لمطلبك حتى لو كان شرحك بـ 100 سطر 😇

 

أو قد نجد مداخلة من أحد الأساتذة والأخوة بالتوضيح والحل دون استخدام الأكواد !!!

تم تعديل منذ 2 ساعات بواسطه Foksh

[كمال على طارق]

طلبى بالضبط ان أستطيع ادخال اى كمية لاى صنف طالما اقل أو يساوى الرصيد المتاح لنفس الصنف

ومنع ادخال اى كمية لأى صنف طالما العدد يتعدى المتاح من هذا الصنف

[Foksh]

طيب .. رغم أن الطلب تم تحقيقه من خلال Data Validation ، ولكن هذا لا يمنع من تجربة الفكرة التالية من خلال الأكواد . حيث تم دمج الحدث عند التغيير للورقة من :-

    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B3:B3000")) Is Nothing Then
        With Target(1, 5)
        .Value = Date & " " & Time
        .EntireColumn.AutoFit
        End With
    End If

ليصبح :-

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Or Target.Row < 3 Then Exit Sub
    If Target.Column = 2 And Target.Value <> "" Then Target.Offset(0, 4).Value = Now
    If (Target.Column = 4 Or Target.Column = 5) And Trim(Me.Cells(Target.Row, "D").Value) = "بيع" Then
        Dim r As Long: r = Target.Row
        Dim itemCode As Variant: itemCode = Me.Cells(r, "B").Value
        Dim qty As Double: qty = Val(Me.Cells(r, "E").Value)
        
        If Not IsEmpty(itemCode) And qty > 0 Then
            Dim stockSheet As Worksheet: Set stockSheet = Worksheets("المخزن")
            Dim findCell As Range
            Set findCell = stockSheet.Range("B3:B800").Find(What:=itemCode, LookIn:=xlValues, LookAt:=xlWhole)
            
            If Not findCell Is Nothing Then
                Dim realAvailableStock As Double
                realAvailableStock = Val(stockSheet.Range("I" & findCell.Row).Value) + qty
                
                If qty > realAvailableStock Then
                    MsgBox "• لا يمكنك إتمام عملية البيع هذه" & vbCrLf & vbCrLf & _
                           "• الصنــف: " & Me.Cells(r, "C").Value & vbCrLf & _
                           "• الرصيد المتاح حالياً: " & realAvailableStock & " قطعة" & vbCrLf & _
                           "• الكمية المطلوبة: " & qty & " قطعة", _
                           vbCritical + vbMsgBoxRight + vbMsgBoxRtlReading, "تجاوز الرصيد"
                    
                    Application.EnableEvents = False
                    Me.Cells(r, "E").ClearContents
                    Me.Cells(r, "E").Select
                    Application.EnableEvents = True
                End If
            End If
        End If
    End If
End Sub

 

ملفك بعد التطبيق :-

مخزن 1.xlsb

[كمال على طارق]

أحسنت استاذنا الكريم وهذا هو المطلوب بالضبط بارك الله فيك وزادك الله من فضله

وياريت كان تم الوصول لهذه النتيجة بـــــ Data Validation

 

[Foksh]

4 دقائق مضت, كمال على طارق said:

وياريت كان تم الوصول لهذه النتيجة بـــــ Data Validation

يمكننا الإنتظار مشاركات جديدة .. فلا تستعجل بإغلاق الموضوع باختيار الإجابة 🙂