• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Complex Dynamic Drop Down menus

G

G_Freak45

New Member
I am trying to make a drop down menu for a sheet i can email to prospective clients where they can easily fill out different options. It would be a vehicle basis and the drop down menus I am creating are to choose make based on year and model based on make and year. I have already attempted this with the following formula in the list filter:


=INDIRECT("'Make Sheet'!" & (INDIRECT("D" & ROW()) - 1950) & ":" &(INDIRECT("D" & ROW()) - 1950))


But this appears to have blank options and certain options missing if they are not available in every row of options. So i revised it into this:


=INDIRECT("'Make Sheet'!B" & (INDIRECT("D" & ROW()) - 1950) & ":" & ADDRESS(INDIRECT("D" & ROW()) - 1950, 1 + INDIRECT("'Make Sheet'!A" & INDIRECT("D" & ROW()) - 1950))))


where the block (INDIRECT("D" & ROW()) - 1950) references the row... so:


=INDIRECT("'Make Sheet'!B" & (row) & ":" & ADDRESS(row, 1 + INDIRECT("'Make Sheet'!A" & row))))


Which now has the issue of only displaying the 1st option (A is used to determine how many are in each row)
 
Hi, G_Freak45!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


It'd be advisable we have the contents of columns A, B & D at least, or much better indeed if you consider uploading a sample file, it'd be very useful for those who read this and might be able to help you. Thank you.


Give a look at the second green sticky post at this forums main page for uploading guidelines.


Regards!
 
Sheet 1 has 4 main collumns, year make model and vin

Make should have a certain drop down menu based on the year (some manufacturers went out of business or were created at certain dates)

Then based on the make and year you should be able to choose certain models


There is a main sheet with the 4 collumns, then a second sheet called "Make Sheet" and a sheet for every make, ie: "GMC Sheet"


This is an example of the values in A, B, C, and D in the Make Sheet


3, Acura, Aston Martin, Audi

2, Acura, Audi,

3, Acura, Bently, Cadilac

1, Acura, ,


Where the first column is the number of makes available and the second and so on columns are the makes themselves. I wanted the drop down menu to show only as many options as the first column, and everything in the rows. The rows are based on year (ROW() + 1950 == year) and the columns are alphabetically organized, i cant submit a file because i dont have it with me.


PS: Thanks for the reply :)
 
I've had a play and come up with something - unfortunately I can't use dropbox at work, so I may have to try and explain what I've done here:


Setup a hierarchy for your data:


My example I used A1:C10


Year Make Model

2011 Audi A3

2011 Audi A5

2011 Mercedes C Class

2011 Volkswagon Golf

2012 Audi A2

2012 Audi A4

2012 BMW Z4

2012 Ford Fiesta

2012 Ford Mondeo


Create the following named ranges to store all the possible entries for each:


CarYear =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$10))

Make =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$10))

Model =OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$C$2:$C$10))


Next will be to generate a unique list of entries


In E2:E10 (E1 Title Year):

=IFERROR(INDEX(CarYear,MATCH(0,COUNTIF($E1:E$1,CarYear),0)),"") + ctrl shift enter

In F2:F10 (f1 title Make)

=IFERROR(INDEX(Make,MATCH(0,COUNTIF($F$1:F1,Make)+(CarYear<>$I$2),0)),"") + ctrl shift enter

In g2:g10 (g1 title Model)

=IFERROR(INDEX(Model,MATCH(0,COUNTIF($G$1:G1,Model)+(CarYear<>$I$2)+(Make<>$J$2),0)),"") + ctrl shift enter


Setup the following named ranges


UniqueCarYear =OFFSET(Sheet1!$E$2, 0, 0, COUNT(IF(Sheet1!$E$2:$E$10="", "", 1)), 1)

uniqueMake =OFFSET(Sheet1!$F$2, 0, 0, COUNT(IF(Sheet1!$F$2:$F$10="", "", 1)), 1)

UniqueModel =OFFSET(Sheet1!$G$2, 0, 0, COUNT(IF(Sheet1!$G$2:$G$10="", "", 1)), 1)


Finally


I2 set data validation to List and select "UniqueCarYear" as source

J2 set data validation to List and select "uniqueMake"as source

K2 set data validation to List and select "UniqueModel" as source


It should allow you to choose any year in I2

J2 Will then show you all makes for that year

K2 Will then onyl show you all models for that make and year
 
Hi ,


Try the following :


Keep your cursor anywhere in the second row on the Make tab , and create the following named range :


Validation_list


and in the refers to box , put in the following formula :


=OFFSET(Make!$A$2,ROW(Make!$A2)-2,1,1,Make!$A2)


Now , in the Sheet1 tab , for your drop-downs , in the cell in the second row , select List , and for the formula , use : =Validation_list.


Copy this downwards.


Now , each drop-down will have as many entries as the values in cells in column A on the Make tab , and the entries will be the data in the cells in columns B , C , D ,... on the Make tab against each number in column A.


Narayan
 
do you mind explaining exactly what was done to me? Id rather learn from this than come back with a similar question or one that can be solved with a similar answer
 
actually I did some research on the offset function and got it working... I used the following:


=OFFSET('Make Sheet'!A1, (INDIRECT("C"&ROW()) - 1951), 1, 1, INDIRECT("'Make Sheet'!$A$"&(INDIRECT("C"&ROW()) - 1950)))


And I used the following for the models of every make:


=OFFSET(INDIRECT("'" & INDIRECT("E" & ROW()) & " Sheet'!A1"), (INDIRECT("C"&ROW()) - 1951), 1, 1, INDIRECT("'Make Sheet'!$A$"&(INDIRECT("C"&ROW()) - 1950)))


Thanks for your help everyone
 
soooo apparently that was incorrect too, it worked for just a couple entries uniform for every row in the make sheet, but once I added a few more something went wrong and now the formula evaluates to an error


this is currently what I have the make sheet:


3, American Coleman,, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

3, American Coleman, American LaFrance, Autocar Company

2 American LaFrance, Autocar Company

2 American LaFrance, Autocar Company

2 American LaFrance, Autocar Company

2 American LaFrance, Autocar Company

2 American LaFrance, Autocar Company

2 American LaFrance, Autocar Company

2 American LaFrance, Autocar Company

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey

3, American LaFrance, Autocar Company, Bailey


PS. sorry about the spam, dont know what tags are for a sheet parse if there is one
 
Hi ,


I have assumed the data on your Make sheet tab is as follows :

[pre]
Code: 
3	 Acura	 Aston Martin	 Audi
2	 Acura	 Audi
3	 Acura	 Bentley         Cadilac
1	 Acura
[/pre]
After defining the named range Validation_list as described in my earlier post , on any other sheet , in any column , on the second row , select Data Validation , select List , and in the Source input box , type in =Validation_list.


Now , the drop-down in this cell should show the three choices Acura , Aston Martin and Audi.


Copy this cell to the cell in the next row ( the third row ). The drop-down in this cell should show only two choices Acura and Audi.


Narayan
 
I tried exactly that and came up with the same issue, when i varying sizes in some instances it showed a blank instead of the text in the last place...
 
Hi ,


Can you upload your workbook ?


Or , can you post the data present on the Make tab in the row where you are facing a problem ? Along with the data , please mention the row number where this is happening , and post the formula used for Data Validation in that cell.


To explain , suppose this problem is in row 77 ;


a) Post the data from row 77 in the Make tab


b) Post the formula from the cell in row 77 in the sheet where you have used the Data Validation drop-down.


Narayan
 
I replied to this but it didnt take...

No i cant legally upload my workbook anymore as it contains legal information that I cant edit nor remove that I would be liable for if I posted it online.


The data list is what I posted above currently, and all the rows are in the post below that, the long one, the first line is 1, 2nd is 2, etc


The formula for each list is the same, that's why I used indirects in it, so that I wouldn't have to adjust the row for the year data cell.
 
You must log in or register to reply here.
Back
Top