CODEWITHSUNDEEP
excelexcel-2010vba
SamPassmore
SamPassmore

Reputation: 1365

Change formula based on selection from dropdown box

I am trying to create a quick way to make changes on an Excel sheet and I was wondering if someone could enlighten me on an efficient way to solve the following problem.

Say I have a line of three numbers, lets say A1:C1 (However, the data is not in neighbouring columns in the real data set):

5, 10, 15

I want to have a dropdown box at the end of that line (A4) which shows a list of functions that could be used on these numbers (For example: SUM, AVERAGE, MAX, MIN) and for that to dictate the formula in A5.

A simple way to do this is to create an IF statement and a dropdown box of the possibilities: e.g. 

= IF($A4 = "average", AVERAGE(A1:A3), IF($A4= "min", MIN(A1:A3), etc..

However this is tedious if there is a large number of function choices. So I was wondering if there was a quick way to do this?

Perhaps something like

= function(A1:A3)

Where function will change dependent on the phrase in the dropdown box, and the dropdown box is contingent on a list of functions held somewhere else on the sheet.

Upvotes: 0

Views: 10723

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149297

Interesting Question. :)

See this example

Let's say your worksheet looks like this

enter image description here

Code:

Paste this in a module

Function GuessFunction(Rng As Range, FuncType As String) As Variant
    Dim wFn As WorksheetFunction

    GuessFunction = "Error - Please Check Input"

    Set wFn = Application.WorksheetFunction

    Select Case UCase(FuncType)
        Case "AVERAGE": GuessFunction = wFn.Average(Rng)
        Case "SUM": GuessFunction = wFn.Sum(Rng)
        Case "MAX": GuessFunction = wFn.Max(Rng)
        Case "COUNT": GuessFunction = wFn.Count(Rng)
        '
        '~~> Add More
        '
    End Select
End Function

Next enter the formula =GuessFunction(A1:H1,I1) in the cell and your are done.

Output:

enter image description here

ALTERNATIVE

Another way to do what you is to use EVALUATE

Function GuessFunction(Rng As Range, FuncType As String) As Variant
    GuessFunction = "Error - Please Check Input"
    GuessFunction = Application.Evaluate("=" & FuncType & "(" & Rng.Address & ")")
End Function

PROS:

You don't have to specify separate code for each formula like we did it in Select Case above.

CONS:

You can only make it work for formulas like SUM/MAX/MIN i.e of type FORMULA(ADDRESS). You cannot use it for say VLOOKUP

Upvotes: 1

pnuts
pnuts

Reputation: 59460

Must you "reinvent the wheel"? Seems all to have been done for you, with Insert > Tables - Table:

SO21819663 example

Upvotes: 2

Related Questions