I want to create a data validation list that automatically updates itself based on the current row. In other words, I want a dropdown in column C with all the values that have been entered into column C for the current value in column A.
I placed this in row 1 of a hidden column for my data validation list range:
=SORT(UNIQUE(FILTER(C3:C,A3:A=C1,NOT(ISBLANK(C3:C)))))
It works perfectly except I have to manually update the value in C1
with the current column A
value. Sub-optimal. I want the condition A3:A=C1
to be A3:A=C + CURRENTROW()
, where CURRENTROW()
is any kind of function or custom function that will return the, you guessed it, current row. I can't find anything that will give me either the current cell or the current row. I have tried the following custom functions:
function CURRENTROW() {
return SpreadsheetApp.getActiveSheet().getActiveRange().getRow();
}
function CURRENTROW() {
return SpreadsheetApp.getActiveSheet().getActiveCell().getRow();
}
function CURRENTROW() {
return SpreadsheetApp.getActiveSheet().getCurrentCell().getRow();
}
All 3 variations return the row number of the cell that contains the function but I want the row number that the user is currently on. Can someone please show me how to get this value? Any help here is greatly appreciated.
Based on your little information you could do something like this, but just works after an edit is made in column c and will update the number in D1:
function onEdit(e) {
if (SpreadsheetApp.getActiveSheet().getActiveRange().getColumn()==3){
var rownum = SpreadsheetApp.getActiveSheet().getActiveRange().getRow()
SpreadsheetApp.getActiveSheet().getRange('D1').setValue(rownum);
}
}