Single drop down with data from separate columns

Question

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.

Answer 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.

Answer 2

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.