Combine and show two list in data validation with if condition

HI,
1. I have a list of "products", "raw materials", "packing materials" each in different sheets
2. and in another i have data validation to show this list with respective to the value in near by cell as "SALE", "PRODUCTS","MANUFACTURING", "PURCHASE"

Now,

1. if the cell has value "SALE" or "PRODUCTS" I want to get the list of "PRODUCTS"
2. If the cell has value "Purchase" or "Manufacturing" i want to show both list "RAW MATERIAL" AND "PACKING MATERIAL"

I found a VBA code mentioned below to do this ,

Option Explicit

Sub Validation_two_ranges()
    Dim a$, el As Range
    Dim rng1 As Range, rng2 As Range
        Set rng1 = Range("c3:c5") 'you can assign by namerange
        Set rng2 = Range("d3:d5")
    For Each el In rng1 'first range
        a = a & el.Value & ","
    Next
    For Each el In rng2 '2nd
        a = a & el.Value & ","
    Next
    With Range("A1").Validation 'destination val.list
        .Delete
        .Add Type:=xlValidateList, Formula1:=a
    End With
        Set rng1 = Nothing
        Set rng2 = Nothing
End Sub.

Now..how do i write formula in data validation for this to work.