Dynamic dropdown in Excel

[BMD4]

Hello All,

I have 2 requirements to create dynamic dropdown in excel when the selections are in the form of a table.

1. Based on my selection of ERP in K5, I should have a drop down for CoCode (J8). This drop down is derived from CoCo tab.In CoCo tab I pull, Col D values as dropdown for col F which is my ERP selection.

2. Based on CoCode in JE sheet, I populate L8 (Loc) in JE sheet. Based on the L8, I should have a drop down in G8(SrcAcct). This is driven from 'Acct' sheet.Col D values are the drop down values based on Col B.

Attached the sheet.

Can you please help on this. Quick help is much appreciated.

Thanks much in advance.

[JohnTopley]

Named ranges

CoCode =OFFSET(CoCo!$F$2,MATCH(JE!$K$4,CoCo!$F$3:$F$14,0),-2,COUNTIF(CoCo!$F$3:$F$14,JE!$K$4))

SrcAcct =OFFSET(Acct!$D$1,MATCH(JE!$L$8,Acct!$C$2:$C$11,0),,COUNTIF(Acct!$C$2:$C$11,JE!$L$8))

G8 Data Validation : List .==>> =SrcAcct

J8 Data Validation List ==>> =CoCode

[BMD4]

Thanks much for quick reply John. This helps for sure.

My actual record count in CoCo sheet is around 45k and Acct sheet is around 450K and is dynamic.

Can you please suggest how can we make the formula dynamic.

[Glenn Kennedy]

To make it fully dynamic:

3 Named ranges (CTRL-F3 to view/edit):

Code:
=$D$3:INDEX($D:$D,AGGREGATE(14,6,MATCH({1E+100,"Zzzz"},$D:$D),1))

ERP
=$F$3:INDEX($F:$F,MATCH("Zzz",$F:$F))

List (for DV)
=$J$3:INDEX($J:$J,SUMPRODUCT(--(LEN($J$3:$J$26)>0)))

In the last one, change the 26 to whatever is realistic... but not to a whole column reference.

For the second one... two named ranges

Acct:
=$D$2:INDEX($D:$D,MATCH("Zzzz",$D:$D))

SRC (for DV):
=$K$3:INDEX($K$3:$K$26,SUMPRODUCT(--(LEN($K$3:$K$26)>0)))

In the last one, change the 26 to whatever is realistic... but not to a whole column reference.

[BMD4]

Thanks much Glenn. I tried your code and see it is not working for CoCode (col J). when I select BH, I am getting only one value instead of 2 and if I select Oscar, I see CoCo, which is not in my list. Can you please help.

[Glenn Kennedy]

Silly, silly mistake on my part. In the SRC and List Named ranges... change all the 25s to whatever is sensible and future proof.

[BMD4]

Hi John,

I have expanded my selection to 30 rows in CoCo sheet and the below logic does not work for the range selected. Can you please help.

CoCode =OFFSET(CoCo!$F$2,MATCH(JE!$K$4,CoCo!$F$3:$F$30,0),-2,COUNTIF(CoCo!$F$3:$F$30,JE!$K$4))

[BMD4]

Thanks for the logic Glenn. I tested it and see it is not working as expected for Acct. eg. The SrcAcct in dropdown is not same as 'MC_SAP_FM'.

Can you please advise. Attached the file.

[menem]

May I ask, is it possible to sort data in
CoCo ( base on column F )
Acct ( base on column C )

if yes , please try

CoCode validation
=IF(COUNTIF(CoCo!$F:$F,JE!$K$4)>0,OFFSET(CoCo!$D$1,MATCH(JE!$K$4,CoCo!$F:$F,0)-1,0,COUNTIF(CoCo!$F:$F,JE!$K$4)),"")

SrcAcct validation
=IF(COUNTIF(Acct!$C:$C,JE!$L8)>0,OFFSET(Acct!$D$1,MATCH(JE!$L8,Acct!$C:$C,0)-1,0,COUNTIF(Acct!$C:$C,JE!$L8)),"")

Regards.

Note : my formula is refered all column , it not good for performance but I usally use them because in my environment
most of worksheet can't determine number of rows. it's become my bad habits.

[Glenn Kennedy]

You've lost me here " The SrcAcct in dropdown is not same as 'MC_SAP_FM'". There seems to be something missing in your description of the requirement (or my understanding of it)!!

Please explain the connection.

[JohnTopley]

Using OFFSET ..

Sort CoCo by ERP and ACCT by PartName

NOTE: Glenn's AGGREGATE formulae do NOT require the data to be sorted.

Named range "Acct" in Glenn's file)

=IFERROR(INDEX(Acct!$D:$D,AGGREGATE(15,6,ROW($D$3:$D$50)/(Acct!$C$3:$C$50=JE!$L$8),ROWS($1:1))),"")

[BMD4]

Thanks so much Menem. This works.