-1

I couldn't word it very well in my question but a detailed explanation will be easier to understand.

I want to define a code -such as "A"- and a result text for that code or in my case I need a date. What I am basically asking is that on a specific column whenever I write "A" and press enter, the corresponding date should fill the cell. I didn't know how to search this so I had to create this question. Preferably, the code and the corresponding text should be on a different worksheet.

I need it on Google Drive, but if not I am okay with an Excel answer as well.

New contributor
Taha Guler is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • In excel you could do this with a Worksheet_Change macro. So, for Excel, you could research that or event-triggered macros. – Ron Rosenfeld Aug 15 at 22:01
  • In Google Sheets the function similar to Worksheet_Change is onEdit(e). – Christofer Weber Aug 16 at 9:44
0

I'm completely new do Google Apps Script and JavaScript, but this seems to work:

/** @OnlyCurrentDoc */
function onEdit(e) {
  var activeSheet = e.source.getActiveSheet();
  var range = e.range;
  var target_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  var target_range = target_sheet.getRange("A1:B10");
  if (activeSheet.getName() == "Sheet1") {
    for (var i = 1; i <= 10; i++) {
      var currentValue = target_range.getCell(i,1).getValue();
      var currentDate = target_range.getCell(i,2).getValue();
      if (currentValue == e.value) {
        range.setValue(currentDate);
        break;
        }
      }
  }
}

How it looks in action:

In action

On Excel VBA, it would look something like:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Set r = Sheets(2).Range("A1:A10")   'This is where I have the "code", with the date in column "B"
If Target.Count = 1 Then
    Set c = r.Find(Target.Value, LookIn:=xlValues)
    If Not c Is Nothing Then Target.Value = c.Offset(0, 1).Value
End If
End Sub

Both of these will search the second sheet in the A1-A10 range for the entered value in sheet 1. And then return the date (or whatever) from column B on the corresponding row.

Your Answer

Taha Guler is a new contributor. Be nice, and check out our Code of Conduct.

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.