Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 2nd 20, 08:17 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 20
Default How to generate dynamic drop down

Hi,

I have a list of Pincodes, Area, City and State names. (Total rows are some 150000)

Now, when i select Pincode, in the next column, i need Area to be a drop down which is generate dynamically based on pincode selected.

Can you guys please help me.

Thanks
Anil Hungund.

  #2   Report Post  
Old April 2nd 20, 12:25 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,182
Default How to generate dynamic drop down

Hi,

I have a list of Pincodes, Area, City and State names. (Total rows are some
150000)

Now, when i select Pincode, in the next column, i need Area to be a drop down
which is generate dynamically based on pincode selected.

Can you guys please help me.

Thanks
Anil Hungund.


See here...

https://www.contextures.com/exceldat...nddynamic.html

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #3   Report Post  
Old April 3rd 20, 08:20 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 20
Default How to generate dynamic drop down


Thanks for quick response,

But for some reason Filter function is not working for me. is there any alternative.





On Thursday, 2 April 2020 16:55:31 UTC+5:30, GS wrote:
Hi,

I have a list of Pincodes, Area, City and State names. (Total rows are some
150000)

Now, when i select Pincode, in the next column, i need Area to be a drop down
which is generate dynamically based on pincode selected.

Can you guys please help me.

Thanks
Anil Hungund.


See here...

https://www.contextures.com/exceldat...nddynamic.html

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #4   Report Post  
Old April 3rd 20, 03:28 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,182
Default How to generate dynamic drop down

But for some reason Filter function is not working for me. is there any
alternative.


What does the Filter function do, exactly?

If you are using the 'Dependant Dropdowns' example I linked to.., there is no
Filter function. As you describe what you want to do, each Pincode selected
must also be the DefinedName of the range that contains its dropdown list.

If the list is NOT on the sheet containing the Dependant Dropdowns, the
DefinedName must be workbook level (global scope). Otherwise, the DefinedName
can be sheet level (local scope)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #5   Report Post  
Old December 31st 20, 02:51 PM
Junior Member
 
First recorded activity by ExcelBanter: Dec 2020
Posts: 23
Default how to create a dynamic drop down list in excel

To create a dynamic dependent drop down list in Excel, carry out these steps:

1. Get items for the main drop down list
For starters, we shall extract all different fruit names from column A. This can be done by using the UNIQUE function in its simplest form - supply the fruit list for the first argument (array) and omit the remaining optional arguments as their defaults work just fine for us:
2. Create the main drop down
To make your primary drop-down list, configure an Excel Data Validation rule in this way:

Select a cell in which you want the dropdown to appear (D3 in our case).
On the Data tab, in the Data Tools group, click Data Validation.
In the Data Validation dialog box, do the following:
Under Allow, select List.
In the Source box, enter the reference to the spill range output by the UNIQUE formula. For this, type the hash tag right after the cell reference, like this: =$G$3#
This is called a spill range reference, and this syntax refers to the entire range regardless of how much it expands or contracts.

Click OK to close the dialog.
Get items for the dependent drop down list
To get entries for the secondary dropdown menu, we'll filter the values in column B based on the value selected in the first dropdown. This can be done with the help of another dynamic array function called FILTER:

=FILTER(B3:B15, A3:A15=D3)

Where B3:B15 are the source data for your dependent drop down, A3:A15 are the source data for your main dropdown, and D3 is the main dropdown cell.

To make sure the formula works correctly, you can select some value in the first drop-down list and observe the results returned by FILTER. Make the dependent drop down
To create the second dropdown list, configure the data validation criteria exactly as you did for the first drop down at step 2.

I hope this helps!
Tim Paine


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
vlook to generate a drop down menu Tacklemom Excel Discussion (Misc queries) 1 May 28th 09 10:22 PM
Generate Dynamic Charts Sheikh Saadi Excel Discussion (Misc queries) 1 November 7th 08 12:35 AM
Generate date in cell on changing value in drop down list Hannes Excel Programming 7 August 27th 08 01:39 PM
how do generate a drop down list with no blanks GMRJEN Excel Discussion (Misc queries) 2 April 27th 07 02:20 AM
How do I generate Errors for Drop-Down Lists? nelli Excel Discussion (Misc queries) 5 May 26th 06 01:35 PM


All times are GMT +1. The time now is 08:21 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017