[Help] Dependent DDL with Index Match function
Hi,
I have created small list of company products. I want it in the form of DDL. So in sheet 2 (choice list) I have done all data entry and on sheet 1 (company product) I want DDL. On sheet 1 I have done company list DDL, problem is dependent drop drown list with product name. I want it with Index Match function. I'm very new to excel and Index Match functions goes over my head. Please can experts here me to achieve the result.
Please check attachment.
Thanks.
1) Remove column A on ChoiceList
2) Enter your primary options across row 1 exactly as you wish them to appear in the primary DDL. The options for each list should be directly below the titles, just as you have them now.
3) Create a single Named range called Companies which is the entire row1 of ChoiceList
Now your ready for Dynamic Dependent Drop Down Lists, we will not be using INDIRECT() as that doesn't work with dynamic ranges.
4) On ProductPrice cell B1, use these DV settings:
Allow: List
Source: =OFFSET(Companies, , , , COUNTA(Companies))
4) In cell B2 use these DV settings:
Allow: List
Source: =OFFSET(Companies, 1, MATCH(B1, Companies, 0)-1, COUNTA(OFFSET(Companies, 1, MATCH(B1, Companies, 0)-1, 10000, 1)), 1)
(If cell B1 is currently empty, you make get an error, that's OK.)
Now try your new DDLs.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
@JBeaucaire Thanks for kind reply and taking time to give solution. Basically I want to run the file in adroid phone and DDL is supported in e droid cell pro app. Indirect function is not supported in app and solution given by you with offset function also doesn't seem to work. That is why I'm requesting help with Index/Match function. Please do reply. Thanks.
Where did you determine the OFFSET formula is not supported? What is the actual name of this app? OFFSET is "the alternative" solution that works for dynamic dependent named range creation for dynamic dependent cascading drop down boxes. I don't really know a way to accomplish it without OFFSET.
App name is e-droid-cell-pro
I know of no way to create dependent dynamic drop down lists without using OFFSET. If they are not dynamic, you can use INDIRECT().
INDEX/MATCH can be used, but OFFSET() is still included in the process to determine the bottom of the range.
can't be sure this will work on your android app but it uses index and match ;-)
Josie
if at first you don't succeed try doing it the way your wife told you to
Named ranges:
CompanyList: =ChoiceList!$A$1:$D$1
CompanyLookup: =ProductPrice!$B$1
Prodlist: =ChoiceList!$A$2:$D$6
Data Val:
ProductList!B1: =CompanyList
ProductList!B2: =INDEX(Prodlist,,MATCH(CompanyLookup,CompanyList,0))
Ben Van Johnson
Here is something else that you might be able to try. For some reason, my computer balked at saving your xls file before working with it. This was created with Excel 2010.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
@NewDoverman - the functions INDIRECT() and OFFSET() have been discounted from use, see posts 2-6.
@JBeauclaire
I saw that about INDIRECT and offset but from your posts of 4 and 6, I was not convinced that the problem was with the functions but something else that was not known.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks