3

I have a spreadsheet that I need to add new data to but the previous user made it so that I can't add data in a new row without moving down a lot of data which would mess with all the calculations.

What I am trying to do is to make it so I can add the new data to the side instead and still have the same drop down access the two separate columns. I attached a screen cap of an example of what I want below.

I have tried everything I can think of and all my searches come back with putting multiple columns in a drop down instead of just the data from them.

As you can see below the example has data in D1 to D5 and F1 to F5. If I wanted the dropdown to have the data from those columns how would I do it?

EDIT: Just to clarify there is data between the columns in the actual page which is why I can't just highlight all of it.

Example

  • @pnuts That could be a solution. If I cannot find a way to do what I am attempting I will likely do that. – Aaron May 20 '14 at 15:12
  • @pnuts Actually that wouldn't work because the data that is already there has to remain where it is as well. – Aaron May 20 '14 at 15:14
  • @pnuts There is a lot of calculations dependent on the data remaining where it is. I could go that route but I know I would miss changing a calculation somewhere. I am in chat if you want to talk further. – Aaron May 20 '14 at 15:23
  • Hi Aaron, I've updated the code to cope with old and new sheets. – Jacob Jan Tuinstra Jun 3 '14 at 13:45
1

With the below script you can build a validator from multiple ranges, in both old and new style Google Spreadsheets and you can select different sheets.

Code

// create menu item
function onOpen() {
  createMenu('Validation', 'Add validation to active cell', 'myValidation');
}

// add validation to active cell
function myValidation() {
  var ss = SpreadsheetApp.getActive(), output = [];

  // create input box or prompt
  var int = getPrompt('How many ranges do you want to combine?', 'Add integer');

  // collect data
  output = getData(1);
  for(var i=1; i<int; i++) {
    output.push.apply(output, getData(i+1));
  }

  // build validation
  var rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(output.sort(sortNumber)).build();
  ss.getActiveCell().setDataValidation(rule);
}

// show prompt and retrieve the corresponding data
function getData(int) {
  var ss = SpreadsheetApp.getActive(), text, values;

  // create input box or prompt
  var text = getPrompt('Range like A1:A4 or Sheet1!A1:A4', 'Range ' + int).split('!');  

  // check if reference to other sheet is made  
  if(text.length > 1) {    
    values = ss.getSheetByName(text[0]).getRange(text[1]).getValues();
  } else {
    values = ss.getRange(text).getValues();
  } 

  // check for entries
  if(values[0].length > 1) {
    throw 'Only single columns are to be used';
    return;
  } 

  // return the data
  return values;    
}

function createMenu(menuName, itemName, funcName) {
  try {
    SpreadsheetApp.getUi()
      .createMenu(menuName).addItem(itemName, funcName).addToUi();
  } catch(e) {
    SpreadsheetApp.getActive()
      .addMenu(menuName, [{name: itemName, functionName: funcName}]);    
  }
}

function getPrompt(title, header) {
  // create input box or prompt
  try {
    return SpreadsheetApp.getUi().prompt(title, header, app.ButtonSet.OK).getResponseText();
  } catch(e) {
    return Browser.inputBox(title, header, Browser.Buttons.OK);
  }
}

// http://www.devcurry.com/2010/08/sort-numeric-array-in-javascript.html
function sortNumber(num1, num2) {
  return num1 - num2;
}

Example

I've created an example file for you: validator from two ranges
Add the script under Tools>Script editor and press the bug button to activate the authentication.

  • The code works on the example doc you showed me but will not work on the document I am attempting to use. Each time it gives me the error "This operation is not supported (line 2, file "Code")" I can't see any reason it would not work but maybe I am missing something. – Aaron Jun 3 '14 at 13:01
  • It works on newly created documents. It must be something about the sheet I am trying to use. There was no custom code on the sheet so it can't be variable name duplicates. – Aaron Jun 3 '14 at 13:07
  • @Aaron That's most probably due to the fact that you use the old Google Spreadsheets.... – Jacob Jan Tuinstra Jun 3 '14 at 13:11
  • Right. The spread sheet I have is in the old version. I have to find a way to copy the sheet into the new version without destroying the format and calculations which is proving to be impossible so far. – Aaron Jun 3 '14 at 13:54
  • Oh you updated the code. Thank you very much it works now! – Aaron Jun 3 '14 at 13:58
1

Make a helper-column somewhere, eg H1 = d1 .. h5=d5, h6=e1, ... h10=e5

And then just drive your drop-down contents from the helper column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.