0

I have a sheet("validazione") that contain Last Names(col A) and relative First Names(col B) The list is huge, so many last names are in the list more than one time with different first name. In the sheet "validazione" i also tried to make last names "unique" and transpose first names in different columns if maybe is easier to get data.

What i want is to made a such of control of data inserted in the sheet "casting" using data validation. About last names i got it. so when i run the function it fill data validation based on last name source. But is about first names that i have problem: i want to have a filtered data validation there, that it will show me just the first names related to last names. For example if i have smith john, smith ron, smith jane when i digit or i paste smith in last names, i want that are showed just john,ron,jane and not all the first names in database.

I hope you understood and that you can help me.

here down i have a link to the sheet, and i will paste the code

thank you in advance

https://docs.google.com/spreadsheets/d/1Yv4v3JJCXi_ipAb30aTwsKzZuloeVlluLfLslLmie8Q/edit#gid=0

function setValidation() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var validationSheet = ss.getSheetByName("APPOGGIO DATABASE");
  var dataSheet = ss.getSheetByName("CASTING");

  // Impostazione della convalida dati per i cognomi
  var rangeCognomi = dataSheet.getRange("B3:B1000");
  var cognomiRule = SpreadsheetApp.newDataValidation().setAllowInvalid(false).requireValueInRange(validationSheet.getRange("D2:D"), true).build();
  rangeCognomi.clearDataValidations();
  rangeCognomi.setDataValidation(cognomiRule);
  var casting = dataSheet.getActiveRange().getValues()
  var dati = ss.getRange("D2:E").getValues()
      var nFiltrati = dati.filter(function(filtro){return filtro[0] == casting }) 
  var convNomi = nFiltrati.map(function(list){return list[1] })    
  var cdNomi = SpreadsheetApp.newDataValidation().requireValueInList(convNomi).build()
  ss.getActiveRange().setDataValidation(cdNomi)
 
    }

i tried to use a support sheet for get range but i can't understand the logical steps to get a filtered value in a single cell. I am a newbie of coding, so i am sure that someone could give me the right way to work on

Tedinoz
  • 5,911
  • 3
  • 25
  • 35
diblax
  • 1
  • 1
  • I would be interested in your research for this question. This is a popular topic - the technique is known as dynamic dependant dropdown lists. One of the most popular posts on this is : [How do you do dynamic / dependent drop downs in Google Sheets?](https://stackoverflow.com/q/21744547/1330560) but you will find many other examples. Normally I would say that one of these solutions would be appropriate for you except you said _"The list is huge"_. Depending on your definition of huge, it might be that a slightly different/tailored approach is a better way to find matches/make selections. – Tedinoz Mar 08 '23 at 00:19
  • thank you i will take a look...about uge i mean almost 10k users in database. A number that will increase each week with 10/20 new – diblax Mar 08 '23 at 09:35

0 Answers0