Geek asks and answers

Excel: dependent decay lists for dynamic content

I imported the list of customers and their websites from the Google Analytics API (GA) in the following format:

Account     Profile
Client 1    www.client1.com
Client 1    sub.client1.com
Client 1    tst.client1.com
Client 2    www.client2.com
Client 3    www.client3.com
Client 3    sub.client3.com

I need a drop-down list with unique account names, and then a second drop-down list with dependent profiles of the selected account. Something like this:

Account     Profile
Client 1    www.client1.com
            sub.client1.com
            tst.client1.com

We are constantly adding accounts and profiles to GA, so the list should be updated every time we run the report.

How should I do it?

+3
source share
1 answer

As long as you have this list sorted by account , you can use the mixture Offset CountAto provide you with all the ranges you need.

The steps will be:

. " " ( VBA , , - , VBA ). - :

    Sub FindUniqueValues(SourceRange As Range, TargetCell As Range)
        SourceRange.AdvancedFilter xlFilterCopy, , TargetCell, True
    End Sub

( workheet_change , vba G.A., )

. Named Range (Say AccountsRange), forumla =$D$2:INDEX($D$2:$D$5000,COUNTA($D$2:$D$5000)) - , D1 TargetCell , 4999 ( - - , D2)

. AccountsRange . . , E2, , ComboBox

. : ProfilesRange =INDEX($B$1:$B$5000,MATCH($E$2,$A$1:$A$5000,0),1):INDEX($B$1:$B$5000,COUNTIF($A‌​$1:$A$5000,$E$2)+MATCH($E$2,$A$1:$A$5000,0)-1,1) : , COUNTIF($A, excel , $A$1 COUNTIF,

A2 - G.A. (A1 , 4999 ) E2 - Comboboxed AccountsRange. -1 in Match , 0 , 1, Match. 1 - ( , ). COUNTIF :) , .

. , ProfilesRange , .

, :

  • " ", , , , . ( , - VBA , , E2.

  • ProfilesRange. . , ProfilesRange , , , AccountsRange, ProfilesRange - , , )

osknows forumlae ( ): OFFSET . = OFFSET ($ D $2,0,0, COUNTA ($ D $2: $D $5000) $D $2: INDEX ($ D $2: $D $5000, COUNTA ($ D $2: $D $5000) ) ( , Offset forumlae, )

+4

More articles:


All Articles