Data Validation - Subtotal Formula

Question

Hi all,

So I was taking a look at some excel templates and I open this one: Sales Tracker (https://templates.office.com/en-us/Online-Sales-Tracker-TM10000095 )

One really cool feature is the fact that you can use drop-down list to select the reference_num or function_num of the Subtotal formula. How can I do it in other excel file?

Basically when you create the formula: =Subtotal( this number; reference)

Then on the same cell of the formula above you also have a drop-down menu with: Average; Sum; Count

After your selection, it changes the formula above into the right function_num.

For instance:

Cell A1:

=SUBTOTAL(101;Table_1) --> It's the average

On cell A1 I have a drop down menu with all the function and after I select it, it changes the "101" on the formula from the example.

Select Count and then the formula change to =SUBTOTAL(102;Table_1).

Do you have anything in mind on how could I do something like this?

It's just a wonderful feature and it would be great to create these kind of Automated Cell Calculation Drop-down List.

Thank you.

Best regards,

Answer 1

There are two basic ways to accomplish this. One way uses macros (you will have to macro enable your workbook) with this macro in the code behind the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("A1")) Is Nothing Then

        Application.EnableEvents = False

        Select Case Me.Range("A1").Value

            Case "A"

                Me.Range("A1").Formula = "=1"

            Case "B"

                Me.Range("A1").Formula = "=2"

        End Select

        Application.EnableEvents = True

    End If

End Sub

And, in cell A1, set up a dropdown validation list with the list of functions.

Another way without macros is to use a second cell for the dropdown and then, in the formula cell, use this formula:

=CHOOSE(MATCH(B1,{"A","B"},0),1,2)

And in cell B1 put a validation list drop down listing the various types of functions you want to use.

Kevin

Answer 2

Hi Zorvek. Thank you so much, it works! :)