The Function below allows a user to enter the following formula in MS Excel =ABB(A1,A2,A3,A4,A5). This formula will call the Function below which will lookup each variable in the desired Range and ultimately return the desired value. However, to make this work...the user has to import the source table from SQL Server into their local Excel workbook.
The job needed is to leave the source table in SQL Server 2012 and still write the formula in MS Excel =ABB(A1,A2,A3,A4,A5). Essentially the need is to take the data from SQL Server and feed into Excel based upon criteria outlined in the formula. The solution needs to be provided in the form of a xlam.add-in. The connection string to SQL Server is a variable that I will fill in.
Function ABB(dCat As String, dYear As Long, dTime As String, dScale As Variant, dBucket As Variant) As Double
Dim rng As Range
Dim rng1 As Range
Set rng = IIf(dTime = "QTR", Range("R_5"), Range("R_4"))
Set rng1 = IIf(IsNumeric(dBucket), Range("R_7"), Range("R_3"))
dScale = IIf(dTime = "YTD", "<=" & dScale, dScale)
ABB = Application.WorksheetFunction.SumIfs(Range("R_1"), Range("R_6"), dCat, Range("R_2"), dYear, rng, dScale, rng1, dBucket)