2

Using Google Sheets. In one cell, I have a dropdown list that is generated from a concatenation of integer ID column and name column. To get the value of the integer from the selection, I have another cell with this formula:

=LEFT(N1, FIND(" ",N1))

Is there a way that the formula can be used inside the same cell that has the dropdown, without getting a circular dependency error?

  • I don't think this is possible. Or desirable: a dropdown cell is meant for user input, and shouldn't have a formula in it. – user79865 May 3 '15 at 5:31
  • My workaround was to have another cell extract the value. – ImproperUsername May 4 '15 at 14:26
1

This can be done with Google Script. An example is here.

function onEdit(e) {

var ss = e.source;
var s = ss.getSheetByName("Testing");
var r = e.range;

  // to let you modify where the action and move columns are in the form responses sheet
var actionCol = 1;

  // Get the row and column of the active cell.
var rowIndex = r.getRowIndex();
var colIndex = r.getColumnIndex();
  // Get the number of columns in the active sheet.
  // -1 to drop our action/status column
  //var colNumber = s.getLastColumn()-1; //not needed because we are limiting the edit to a single row and cell

var curCell = s.getRange(rowIndex,1, 1, 1); //the same cell that has dropdown but can be a different one

if (e.value !== "" && colIndex == actionCol) {
var curCellValue =  curCell.getValue();
var curCellLeft = curCellValue.split(" ");
var val = curCellLeft[0];
//is it a number?
var matches = val.match(/\d+/g);
if (matches != null) {
 curCell.setValue(val);
}
}
}
-4

=iferror(LEFT(N1, FIND(" ",N1)),"")

  • 1
    What is this? Can you explain this? – serenesat Nov 15 '15 at 15:11
  • 1
    This doesn't do anything, you can't have this formula in cell N1. – user79865 Nov 15 '15 at 17:00

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.