Conditional, Dynamic Lists

[alphaexcel]

I am trying to create a dynamic, unique listing. I have two data lists (expanding 45,000 rows). They are as follows. I CANNOT use macros due to security constraints :

ARGENTINE PESO ··············Cash Account 1
ARGENTINE PESO ··············Cash Account 2
ARGENTINE PESO ··············Cash Account 3
AUSTRALIAN DOLLAR ·········Cash Account 1
AUSTRALIAN DOLLAR ·········Cash Account 4
AUSTRALIAN DOLLAR ·········Cash Account 7
AUSTRALIAN DOLLAR ·········Cash Account 9
AUSTRALIAN DOLLAR ·········Cash Account 15

I basically want to be able to type "Cash Account 1" in my control cell, and have a dynamic dropdown created that only summarises those currencies available in that cash account, so in this instance, it would be both ARGENTINE PESO and AUSTRALIAN DOLLAR. I have created a solution, but due to the 45,000 row range, it is very processor heavy, and I would like to be quicker i.e. type the cash account you want to analyse, and the combo box / data validation dropdown(?) will only show relevant currencies. I do not mind using helper cells and / or a master sheet to analyse each currency for true/false. Also, the columns can be reversed if that makes it easier. Thanks..

 

[DonkeyOte]

Glad you have it working -- that said I don't think you need worry about the COUNTA and having different formula in MAIN!F... if you ensure first formula in F goes in row 1 you can just the row id to determine the k in SMALL function

=IF(ISERROR(SMALL(LISTS!C:C,ROW(F1))),"",SMALL(LISTS!C:C,ROW(F1)))

and copy down for all rows in F that require formulae.

so F1 will look for k = 1 as ROW(F1) = 1, F2 will look for k = 2 as row(f2) = 2 etc...

 

[alphaexcel]

I was going to try that but it cannot start in row 1 unfortunately. I will prob tidy this up some more going forward.

Thanks for your help...see what else i can do with the SMALL / LARGE functions...

L

 

[DonkeyOte]

OK, you can obviously just alter the ROW function to offset from wherever you do choose to start your validation range -- say row 7 = ROW(F7)-6 and copy down etc... still quicker than running a COUNTA function x times I suspect.