Excel drop down

[BMD4]

Hello All,

I have a scenario where I have 2 columns Code and Scenario.
Based on the scenario value selected, I need to get the corresponding codes in a drop down.
Attached the excel sheet for details.
Could anyone help on this.

Thanks in advance.

[jindon]

One way,
to Sheet1 code module

Please Login or Register  to view this content.

[BMD4]

Thanks much for the reply Jindon. This works as expected.

I have couple of other scenarios.

1. The selection and the dropdown and the list of sources/codes are in 2 sheets.
2. I have source selection and drop down for each and every row.

Attached the excel sheet. Can you please advise on this.
Thanks in advance,

[jindon]

I don’t understand what you are asking.

[BMD4]

Sorry Jindon for being not clear.

I meant

1. Details for source and code are in one tab and the drop down will appear in another tab. Drop down should pick values for the second tab.
2. The source and code selection are for each row. For eg: In row 1, if source is ERP, 1001,1002 and 1004 are in drop down. In row 2, if source is SAP, the drop down values should be AB1,AB2 and AB11.

Attached the excel for your reference.

[jindon]

No attachment.
If the drop down in Sheet1 E5, and the list is in Sheet2 A4:B4 down.
Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[BMD4]

Hi Jindon, Attached the attachment. Please let me know if you still cannot see attachment. Thank you.

[jindon]

Nope,.........

[BMD4]

Hi Jindon, Please let me know if you see it now.

[jindon]

I didn't look at post #5...
Change to

Please Login or Register  to view this content.

[BMD4]

Hi Jindon,

Thanks for the reply.

I have tried the code. It works for first source which is ERP. But the drop down doesn't seem to be working for 'SAP' source. Attached the file. Can you please let me know if I missing anything.

Thanks in advance.

[jindon]

Is is working to me.

B5 validation list will change when you change C5,
Likewise
B6 validation list will change when you change C6 and so on.

This is how I read your question.

[BMD4]

Thanks much for the clarification Jindon. The code works as expected.
Have one question on the code that I am trying to understand.
If I have to change the column of validation list from B to E, where should we update the column number.

[jindon]

Just change 0 to 2

Please Login or Register  to view this content.

r(Row, Column): default value = r(1.1), so when omitted r is r(1,1)
r(,2) = r(1,2) one column to the right.

[BMD4]

Thanks Jindon. I have used this in my sheet and I see the code is not picking all the values in the drop down. Attached the excel file.

Please let me know if I have missed out anything.

Thanks in advance.

[jindon]

Confusing,

You have 3 dropdowns Col B, C and D.
Which one are you talking about?

[BMD4]

Hi Jindon, sorry for the confusion. Based on column C, which is a drop down from 'Acct Map' sheet, Col B should give the matching SRC in the drop down (Col B should look for SRC in 'Acct Map'). I see that is not happening now.

[jindon]

Then use the code in post #10

Please Login or Register  to view this content.

[BMD4]

Sorry to bother you again Jindon. I think, I am missing some thing. when I try to get dropdown enabled in col'G' based on the value selected in col 'L'. I do not see the drop down.
It should look for 'Acct_map' sheet in the file and populate col 'G' drop down. Attached the file.

Can you please let me know if I am missing anything.

[jindon]

Please Login or Register  to view this content.

[BMD4]

Thank you very much Jindon. I tested the logic and seems not to be working for some scenarios. Even if Col L changes based on my selection of Col J, drop down in G is not updated properly. Also, I see for number that starts with 0, 0 is not coming in the number. Can you please let me know if I am missing anything.

[jindon]

Why are you changing time to time?

Is this the last change?

[BMD4]

Apologies about it. I am trying to develop final code for my requirement based on the code provided. The actual requirement is

1. Based on the value in K4, Col J drop down should be populated. This details of these are in CoCo_Map. Based on Col G in CoCo_Map, SRCKEY(Col D) should be populated as drop down in Col J of JE tab.
2. Based on Col J value, I am populating Col L in JE tab using Index function.
3. Now, based on Col L value that is populated using formula in JE tab, I should have drop down in Col G populated. The source of this is in Acct_Map.
In Acct_Map, I should have src populated in JE tab based on the Assignment(Col L) value.

Attached the updated template with all the details.

Please let me know if anything is unclear.

[jindon]

try change to

Please Login or Register  to view this content.

[BMD4]

Thanks a lot Jindon. This worked. Can I use same logic to populate values in Col J based on cell K4. This is driven by CoCo_Map. K4 in JE tab is col G and based on it I should have SRCKEY of CoCo_Map populated as drop down in JE tab in Col J.

Also, is there a limit on the number of cells/rows that I can have. My Actual Acct_Map has almost 500,000 record.

[jindon]

Also, is there a limit on the number of cells/rows that I can have. My Actual Acct_Map has almost 500,000 record.

Create a userform.
500,000 record is too much for the in-cell dropdown.

[BMD4]

Hi Jindon,

My total range is can go till 500,000

x = Join(Filter(Sheets("Acct_Map").Evaluate("transpose(if(c2:c422765='" & _
Me.Name & "'!" & r(, 3).Address & ",d2:d422765))"), False, 0), ",")
If Len(x) Then .Validation.Add 3, Formula1:=x


when I change my cell range to 422765, I am getting below error at 'Validation.Add 3, Formula1:=x' line. can you please suggest.


Runtime error 1004

Application defined or object defined error

[jindon]

Length of the Validation List is about 8120 characters incl comma, so you will need to use other method like filter data and paste to available column/row and reference to that range.

[BMD4]

Hi Jindon,

Does that mean this approach will not work for my record count? Thanks in advance.

[jindon]

Correct, just like you have experienced.

It's not a matter of the number of records, but the length of the list in text delimited by a comma.
It is too risky for such big amount of data anyway.

[jindon]

1) To a Standard code module (Module1)

Please Login or Register  to view this content.

2) To Sheet "JE" code module

Please Login or Register  to view this content.

3) To Sheet "CoCo_Map" code module

Please Login or Register  to view this content.

4) To Sheet "Acct_Map" code module

Please Login or Register  to view this content.

5) To Thisworkbook code module

Please Login or Register  to view this content.

[BMD4]

Thanks so much Jindon. I will check this logic with my record count and will get back to you.

[BMD4]

Thanks a ton for your patience Jindon. This worked for the data set I tried. I will try for the full data set.