CODEWITHSUNDEEP
validationgoogle-apps-scriptgoogle-sheetsscripting
Ali
Ali

Reputation: 71

EDIT - How to copy paste data validation with change in range?

So I made a dynamic data validation drop-down list based on the data validation in the cell next to it. The problem is that when I copy this data validation to a 1000 rows I want it to change the range depending on the row. I know it has to be done manually but was wondering if it can be automated?

So basically what I have is that A1 is the service you choose, and then B1 gives you the option to choose the sub-service from the formula I created which is basically

=TRANSPOSE(SI(ESTVIDE(Prestations!$C7);;SI(Prestations!$C7='Données'!$B$28;INDIRECT("Depose");SI(Prestations!$C7='Données'!$E$28;INDIRECT("Sols");SI(Prestations!$C7='Données'!$H$28;INDIRECT("Cloisons");SI(Prestations!$C7='Données'!$K$28;INDIRECT("Peinture");SI(Prestations!$C7='Données'!$N$28;INDIRECT("Plafonds");SI(Prestations!$C7='Données'!$Q$28;INDIRECT("Plomberie_CVC");SI(Prestations!$C7='Données'!$T$28;INDIRECT("Autres");SI(Prestations!$C7='Données'!$W$28;INDIRECT("Cas_Specifique");"Prestation n'existe pas"))))))))))

The code is in french sorry about that. So basically SI=IF, ESTVIDE=ISBLANK and the ,s are ;s. Prestations!$C7 is the service so basically our "A1" and then enclosed within the INDIRECTS are the named ranges for the sub-services. 'Données'!B28:W28 is basically me comparing the name of the service in the A1 dropdown.

And then I just copy-pasted this over a 1000 rows. So this is basically let's say our E1 and then copied till E1000. In data validation, I just put the range as E1:Z1. I used transpose because otherwise, the data would clash with the copied formulas beneath.

Now what I need is the data validation for A2 onward. Of course, when I copy-paste the data validation it takes the same range E1:Z1. Can it be modified to become E2:Z2, E3:Z3 and so on?

Edit: Here's a link to the document Prestation_Test

Upvotes: 1

Views: 1175

Answers (2)

Ali
Ali

Reputation: 71

I figured it out how to do it through Google Apps Script. If anyone comes across a problem like this, I'd like to put the solution out there:

So what I did is that I used the for function to change the row number both for the range to which I had to apply the Data Validation and the range for the Data Validation itself.

function DataValidation() {



  for (var rowC = 1; rowC < 994; rowC++) {

    var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DV").getRange(rowC, 1, 1, 30);
    var validation = SpreadsheetApp.newDataValidation().requireValueInRange(range, true).build();
    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(rowC+5, 4).setDataValidation(validation);

  }
}

Upvotes: 0

Gravitate
Gravitate

Reputation: 3064

EDIT - Please note that this answer was posted when the question was still tagged with "Excel".

OK. If I understand correctly, one way to do what you want would be to define a dynamic named range, using the current cell row.

Define a named range called "DataValidation" with the formula:

=INDEX(Sheet1!$E:$E,ROW()):INDEX(Sheet1!$Z:$Z,ROW())

You will need to change "Sheet1" to be the name of your sheet containing your E1:Z1000 range (it's not clear from your question which sheet it is on).

Then you can enter 

=DataValidation

into the data validation of a cell and drag that cell down. The data validation will then be based on what ever is in columns E to Z of the current row.

EDIT - How to define a named range

  1. Go to the "Formulas" ribbon.
  2. Click "Name Manager".
  3. Click "New".
  4. Enter "DataValidation" as the name.
  5. Enter the above formula (with the sheet name changed) into the "Refers to:" box.
  6. Click "OK".
  7. Click "Close".

Please see image below:

enter image description here

Upvotes: 1

Related Questions