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]

presumably your lists are not on the same sheet as your control cell ? if not pls provide further info.

EDIT: also if you can't use VBA you can't have combobox controls...

 

[alphaexcel]

That is correct, they are on a different sheet. I have used indexing, counting etc to create gapless, dynamic lists but they are too juicy on the number crunching to create an apparently seamless, instant dynamic list content.

You CAN use Combo Boxes without VBA, there is a seperate FORMS toolbar, that allows the use of formula based Combo boxes that feed off of input cells/ranges and provide the output to a control cell (using Cell Link).

 

[DonkeyOte]

Let's assume the following:

Your lists appear on Sheet LISTS in columns A:B, row 1 onwards

Your control cell appears on Sheet MAIN in Cell A1
Your conditional list should appear on Sheet MAIN in cell A2

On Sheet LISTS in cell C1 enter the following:

=IF($B1=MAIN!$A$1,ROW(C1),1000000)

On Sheet LISTS in cell D1 enter the following:

=IF($B1=MAIN!$A$1,$A1,"")

copy down for all rows of data in your lists.

On Sheet MAIN in say cell Z1 enter the following:

=INDEX(LISTS!$C:$D,MATCH(SMALL(LISTS!$C:$C,ROW(Z1)),LISTS!$C:$C,0),2)

Copy down for as many *possible* currencies you may ever get at one time in your conditional list.

On Sheet MAIN in cell A2 create a validation list and set list range to be MAIN!Z1:Zx where x = last row you created a formula for in above step

Now when you change selection in A1 you should find that the validation list becomes unique.

I don't know of a way you could prevent user from selecting BLANK from the validation list but you will at least be listing available options.


EDIT: Seems like I am just repeating what you have already tried... but I've learnt something new re: forms combobox so not a complete waste of time.

 

[alphaexcel]

Doesn't seem to work, I get a list of currencies that is not unique?

 

[DonkeyOte]

Do you have multiple instances of currency:account ?

ie

AUD : Cash Account 1 combination appears more than once in your list data ?

 

[alphaexcel]

Yes, apologies. This is an extract from a 'fresh' report

<TABLE style="WIDTH: 302pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=402 border=0 x:str><COLGROUP><COL style="WIDTH: 175pt; mso-width-source: userset; mso-width-alt: 8521" width=233><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6180" width=169><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 175pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=233 height=17>ARGENTINE PESO CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 127pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=169>MSCSTPB</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ARGENTINE PESO CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">MSCSTPB</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">DBCSTFX</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">DBCSTFX</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">DBCSTFX</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">FXSWEEP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">FXSWEEP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">FXSWEEP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">FXSWEEP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">FXSWEEP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">FXSWEEP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">FXSWEEP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">FXSWEEP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">FXSWEEP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">FXSWEEP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">FXSWEEP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">JPMCSTPB</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">JPMCSTPB</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">JPMCSTPB</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">JPMCSTPB</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">JPMCSTPB</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">JPMCSTPB</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>AUSTRALIAN DOLLAR CASH</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">JPMCSTPB</TD></TR></TBODY></TABLE>

 

[alphaexcel]

Ah i should be able to sort it now...just took a more indepth look at your formula. I was unaware of the K th property of the SMALL function. Dynamic lists are going to be a breeze from now on

Thanks

L

 

[DonkeyOte]

assuming they are always sorted by Currency Code and Account then in theory you could do the following:

First to reduce formula workload (ie avoid need for countif, matches etc) ensure that the first currency entry appears in row 2 rather than 1.... then change the formula on LISTS sheet in Column C from

=IF($B1=MAIN!$A$1,ROW(C1),1000000)

to

=IF(AND($B2=MAIN!$A$1,TRIM(UPPER($A1&$B1))<>TRIM(UPPER($A2&$B2))),ROW(C2),1000000)

then change formula in LISTS column D from

=IF($B1=Sheet5!$A$1,A1,"")

to

=IF($C2<1000000,$A2,"")

you could get rid of the trim & upper if you know the currency/account data is always returned in the same format for each row -- just a safety catch but if left in will slow down calculations obviously

 

[alphaexcel]

The index/match on MAIN was killing the sheet in terms of processing time. I have found a workaround. On the list sheets, column D is now blank. Column C contains this formula :

=IF(AND($B2=MAIN!$A$1,$A1&$B1<>$A2&$B2),ROW(C2),"")

On the main sheet, column F contains this formula in cell F1 :
=IF(ISERROR(SMALL(LISTS!C:C,1)),"",SMALL(LISTS!C:C,1))

and cells F2 onwards contain this formula :
=IF(ISERROR(SMALL(LISTS!C:C,1+COUNTA($F$1:$F1))),"",SMALL(LISTS!C:C,1+COUNTA($F$1:$F1)))

Cell G on the main contains this formula, which uses the SMALL value as an indirect row value, avoiding any index/matching :
=IF(F1<>"",INDIRECT("LISTS!$A$"&F1),"")

Runs very fast and should solve the timing issue....

Any thoughts?