SOLVED

Data Validation cascading

%3CLINGO-SUB%20id%3D%22lingo-sub-1457530%22%20slang%3D%22en-US%22%3EData%20Validation%20cascading%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1457530%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20form%20to%20fill%20in%20excel%20with%20two%20columns%20First%20Name%20and%20Last%20Name.%20Both%20are%20validation%20lists%20and%20the%20last%20name%20should%20be%20dependent%20on%20the%20value%20of%20the%20first%20name.%20I%20have%20a%20total%20of%20400%20names.%20See%20below%20as%20an%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHidden%20worksheet%20with%20ONLY%20two%20columns%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%2250%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2229px%22%3EFName%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%20height%3D%2229px%22%3E%3CP%3ELName%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2229px%22%3EJohn%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%20height%3D%2229px%22%3EDoe%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2229px%22%3EKevin%3C%2FTD%3E%3CTD%20height%3D%2229px%22%3EHart%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2229px%22%3EKevin%3C%2FTD%3E%3CTD%20height%3D%2229px%22%3EKinsley%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2229px%22%3ERob%3C%2FTD%3E%3CTD%20height%3D%2229px%22%3EHoward%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2229px%22%3EJohn%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%20height%3D%2229px%22%3ERambo%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVisible%20sheet%20for%20the%20user%3C%2FP%3E%3CP%3EFirst%20Name%3A%20%3CU%3EUser%20to%20select%3C%2FU%3E%3C%2FP%3E%3CP%3EDropdown%20List%20available%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2229px%22%3EJohn%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2229px%22%3EKevin%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2229px%22%3ERob%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELast%20Name%3A%20%3CU%3EUser%20to%20select%20based%20on%20the%20first%20name%3C%2FU%3E%3C%2FP%3E%3CP%3EEg.%20If%20the%20user%20selects%20John%2C%20the%20dropdown%20list%20should%20be%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2229px%22%3EDoe%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2229px%22%3ERambo%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESimilarly%20for%20Kevin%20and%20Rob.%3C%2FP%3E%3CP%3EI%20hope%20this%20is%20clear%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1457530%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1457593%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20cascading%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1457593%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F696962%22%20target%3D%22_blank%22%3E%40robustexcel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20you%20go.%20I%20didn't%20create%20the%20form%2C%20but%20assume%20you%20can%20do%20that.%20This%20is%20an%20illustration%20of%20what%20the%20new%20Dynamic%20Array%20functions%20can%20perform.%20I%20used%20FILTER%2C%20UNIQUE%2C%20and%20SORT%20in%20this.%20You%20can%20add%20names%20to%20your%20heart's%20content%20in%20the%20table%20in%20the%20top%20left%20corner%20of%20the%20sheet.%20The%20drop-downs%20will%20automatically%20include%20the%20new%20ones%2C%20in%20alphabetical%20order.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnjoy!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1457599%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20cascading%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1457599%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20helping%20out.%26nbsp%3B%20I%20am%20getting%20this%20in%20all%20the%20cells%20that%20you%20have%20a%20formula%20in.%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%2285%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2285%22%20height%3D%2221%22%3E%23NAME%3F%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20excel%202016%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1457729%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20cascading%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1457729%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F696962%22%20target%3D%22_blank%22%3E%40robustexcel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CEM%3E%22I%20have%20excel%202016%22%3C%2FEM%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThe%20workbook%20uses%20functions%20only%20available%20in%20Office%20365.%3C%2FP%3E%3CP%3EFunctions%20like%20UNIQUE%2C%20FILTER%20and%20SORT%20tend%20to%20be%20manual%20operations%20in%20previous%20versions%20of%20Excel%20and%20are%20not%20that%20easy%20to%20emulate%20as%20formulas.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403294%22%20target%3D%22_blank%22%3E%40mmathes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20like%20the%20attached%20version%20of%20your%20workbook.%26nbsp%3B%20The%20dependency%20between%20first%20and%20last%20names%20works%20both%20ways.%26nbsp%3B%20The%20subordinate%20selection%20usually%20has%20to%20be%20cleared%20before%20the%20main%20selection%20is%20reselected.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1457807%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20cascading%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1457807%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20really%20apologize%20guys.%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20still%20gives%20me%20an%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20my%20excel%20version%20(image%20attached)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efname%3C%2FP%3E%3CP%3E%3D_xlfn._xlws.SORT(_xlfn.UNIQUE(_xlfn._xlws.FILTER(Employee%5BFname%5D%2C%20Employee%5BLname%5D%3DLName.selected%2C%20Employee%5BFname%5D)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elname%3C%2FP%3E%3CP%3E%3D_xlfn._xlws.SORT(_xlfn.UNIQUE(_xlfn._xlws.FILTER(Employee%5BLname%5D%2C%20Employee%5BFname%5D%3DFName.selected%2C%20Employee%5BLname%5D)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eselect%20fname%3C%2FP%3E%3CP%3E%3D_xlfn.ANCHORARRAY(%24H%243)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eselect%20lnname%3C%2FP%3E%3CP%3E%3D_xlfn.ANCHORARRAY(%24D%242)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1458639%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20cascading%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1458639%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F696962%22%20target%3D%22_blank%22%3E%40robustexcel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20might%20work%20in%20Excel%202016%2C%20but%20I%20have%20no%20way%20of%20finding%20out.%26nbsp%3B%20Working%20without%20FILTER%20and%20UNIQUE%20is%20somewhat%20like%20trying%20to%20use%20a%20lawnmower%20to%20trim%20a%20hedge%3B%20simply%20the%20wrong%20tool%20for%20the%20job!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ep.s.%20I%20have%20only%20just%20looked%20at%20the%20file%20you%20attached%20showing%20your%20Excel%20version.%26nbsp%3B%20If%20it%20is%20Office%20365%20monthly%20then%20it%20is%20very%20odd%20that%20the%20original%20files%20do%20not%20work%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1458819%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20cascading%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1458819%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F696962%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40robustexcel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20agree%20with%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%20%3A%20If%20you%20have%20Office%20365%20Pro%20Plus%20(or%20whatever%20it%20was%3B%20that%20was%20the%20idea)%20it%20doesn't%20make%20full%20sense.%20You%20should%20check%20and%20see%20if%20you%20can%20get%20the%20latest%20Dynamic-Array-Ready%20version.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnless%20you're%20in%20a%20government%20office%20where%20it%20takes%20a%20while%3B%20I%20did%20exchange%20a%20similar%20suggestion%20with%20someone%20last%20month.%20She%20had%20the%20latest%20on%20her%20Microsoft%20Surface%20pad%20and%20could%20see%20how%20the%20ARRAY%20functions%20worked%2C%20but%20the%20government%20supplied%20work%20computer%20wasn't%20able%20to%20run%20them%20and%20the%20IT%20department%20would%20take%20a%20while%20to%20get%20to%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThose%20tools%20are%20quite%20astounding.%20This%20YouTube%20video%20is%20a%20great%20intro%20if%20you%20have%20any%20doubts.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have a form to fill in excel with two columns First Name and Last Name. Both are validation lists and the last name should be dependent on the value of the first name. I have a total of 400 names. See below as an example:

 

Hidden worksheet with ONLY two columns:

FName

LName

JohnDoe
KevinHart
KevinKinsley
RobHoward
John Rambo

 

Visible sheet for the user

First Name: User to select

Dropdown List available:

John
Kevin
Rob

 

Last Name: User to select based on the first name

Eg. If the user selects John, the dropdown list should be 

Doe
Rambo

 

Similarly for Kevin and Rob.

I hope this is clear

 

Thank You

6 Replies
Highlighted

@robustexcel 

 

Here you go. I didn't create the form, but assume you can do that. This is an illustration of what the new Dynamic Array functions can perform. I used FILTER, UNIQUE, and SORT in this. You can add names to your heart's content in the table in the top left corner of the sheet. The drop-downs will automatically include the new ones, in alphabetical order.

 

Enjoy!

Highlighted

Hi @mathetes,

 

Thank you for helping out.  I am getting this in all the cells that you have a formula in.

#NAME?

 

I have excel 2016

Highlighted

@robustexcel 

"I have excel 2016"

The workbook uses functions only available in Office 365.

Functions like UNIQUE, FILTER and SORT tend to be manual operations in previous versions of Excel and are not that easy to emulate as formulas.

@mmathes 

You may like the attached version of your workbook.  The dependency between first and last names works both ways.  The subordinate selection usually has to be cleared before the main selection is reselected.

Highlighted

i really apologize guys. 

It still gives me an error.

 

This my excel version (image attached)

 

fname

=_xlfn._xlws.SORT(_xlfn.UNIQUE(_xlfn._xlws.FILTER(Employee[Fname], Employee[Lname]=LName.selected, Employee[Fname])))

 

lname

=_xlfn._xlws.SORT(_xlfn.UNIQUE(_xlfn._xlws.FILTER(Employee[Lname], Employee[Fname]=FName.selected, Employee[Lname])))

 

select fname

=_xlfn.ANCHORARRAY($H$3)

 

select lnname

=_xlfn.ANCHORARRAY($D$2)

Highlighted
Solution

@robustexcel 

The attached might work in Excel 2016, but I have no way of finding out.  Working without FILTER and UNIQUE is somewhat like trying to use a lawnmower to trim a hedge; simply the wrong tool for the job!

 

p.s. I have only just looked at the file you attached showing your Excel version.  If it is Office 365 monthly then it is very odd that the original files do not work for you.

 

Highlighted

@robustexcel 

 

I agree with@Peter Bartholomew : If you have Office 365 Pro Plus (or whatever it was; that was the idea) it doesn't make full sense. You should check and see if you can get the latest Dynamic-Array-Ready version.

 

Unless you're in a government office where it takes a while; I did exchange a similar suggestion with someone last month. She had the latest on her Microsoft Surface pad and could see how the ARRAY functions worked, but the government supplied work computer wasn't able to run them and the IT department would take a while to get to it.

 

Those tools are quite astounding. This YouTube video is a great intro if you have any doubts.

https://www.youtube.com/watch?v=9I9DtFOVPIg