Create Purchase Orders in Excel - Help with linking data

[anandt23]

Hi,

I want to create a purchase order generator system in excel, I have created a basic template and got to a stage where I'm stuck for ideas on how to proceed.

Basically I want the purchase order to work in a way that, once a supplier is selected from a drop down menu, the product codes available within the cell drop down menu relate only to that supplier....so conditional based on supplier chosen.
I have data for each supplier of a separate worksheet and not sure how I can link this together so that once the supplier is selected it brings through products code based on that supplier worksheet.

I hope this makes sense, any questions please let me know.
If any one can help with a solution that would be much appreciated...as I'm not sure this is possible.

I have uploaded a sample file on the following link if it helps.
http://www.filedropper.com/posystemtest

Thanks
Anand

 

[L. Howard]

Looks like a standard dependent dropdown scenario.

I assume dependents will be on sheet "Purchase Order", A18 and on down to A36.

Any reason all the suppliers Product Code, Description, Unit Price info cannot all be on a single sheet? Make the task much simpler.


Howard

 

[L. Howard]

After viewing your workbook, it doesn't look like dependent drop downs will work.

Probably a VBA solution will be needed.

Howard

 

[L. Howard]

Hi Anand,

Here'a a shot at your PO workbook.

https://www.dropbox.com/s/wl5cpszxf7nj9hk/PO SYSTEM TEST exp Drop Box.xlsm?dl=0

It is a "diminished" version of two working sheets where I have removes ALL MERGED cells. Center across selection seems to work much better and causes none of the merged cells problems. I tried to stay somewhat within your column/s and row/s ranges of your original workbook.

There are comments in cells on Purchase Order sheet, read them and should give you an idea of the sheets functions.

Some formulas are defaulted to #NAME? error, where you can restore on a fully working sheet and the Totals formulas and tax added formulas are missing. Easy enough to re-apply where you want them.

There are only four suppliers, a couple have my phony test products and data.

There are some named ranges, so check the Name Manager to familiarize yourself as needed.

So, you will select a supplier in B8 and that supplier's data is brought to the sheet from the Suppliers sheet.

You then select products from the drop down in cells A18:A36 and the products are posted in the PO Order field, an inputbox asks for the Qty for each entry as you go along.

Small RED star in cell G17 will clear (with your permission) the suppliers data and the PO Order field for you to start anew.

Here is the change_event code which is in the Purchase Order sheet module. (and the clear macro)

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B8", "A18:D36")) Is Nothing Or _
    Target.Count > 1 Then Exit Sub


Dim rngFound As Range, pcFound As Range
Dim aRowCount As Long, aColumn As Long, myQty As Long
Dim myFnd As String, pcFnd As String
Dim myCheck


Application.EnableEvents = False

On Error GoTo CleanUp

With Sheets("Suppliers")

    myFnd = Target.Value
    
    Set rngFound = Sheets("Suppliers").Range("A1:Q1").Find(What:=myFnd, _
                  LookIn:=xlValues, LookAt:=xlWhole)

    If Not rngFound Is Nothing Then
       Range("H18:J" & Cells(Rows.Count, "H").End(xlDown).Row).ClearContents
       
       aColumn = rngFound.Column
       aRowCount = .Cells(.Rows.Count, aColumn).End(xlUp).Row

       rngFound.Offset(1, 0).Resize(aRowCount, 3).Copy Sheets("Purchase Order").Range("H18")
       Range("A" & Rows.Count).End(xlUp)(2).Select
     Else
      '
  End If


End With



With Sheets("Purchase Order")

    pcFnd = Target.Value
    
    Set pcFound = Sheets("Purchase Order").Range("$H$18:$J$50").Find(What:=myFnd, _
                  LookIn:=xlValues, LookAt:=xlWhole)

    If Not pcFound Is Nothing Then
      Target.Offset(, 1) = pcFound.Offset(, 1)
      Target.Offset(, 3) = pcFound.Offset(, 2)
      myQty = Application.InputBox("Quanity of " & pcFound & " " & "@ " & pcFound.Offset(, 2) & " ea.")
      Target.Offset(, 2) = myQty
      Target.Offset(1, 0).Activate
      
     Else
      '
    End If


End With

CleanUp:

Application.EnableEvents = True

End Sub


Sub GHI_PO_FIELD_Clear()

Dim myCheck
    myCheck = MsgBox("      Do you want to CLEAR" & vbCr & vbCr & _
                     "Columns H, I, J and PO Field?", vbYesNo)
    If myCheck = vbNo Then
       MsgBox "NO? Okay, bye."
       Exit Sub
    Else
       Range("H18:J" & Cells(Rows.Count, "H").End(xlDown).Row).ClearContents
       Range("A18:D36").ClearContents
       [B8].Activate
    End If

End Sub