Google Sheets: Replace cell contents with another cell contents when “Clicked”?

Question

Google Sheets: Replace cell contents with another cell contents when "Clicked" ?

I'm trying to create an easy way to reveal more data from a row when any cell is clicked from the row it lives in. Currently, I have a nice =VLOOKUP("*") ... function that allows me to type in a COMPANY name into a "search bar" cell, and then VLOOKUP will display cell contents depending on which COMPANY it is.

You can see it in action here: Google Sheets Demo

How To Use:

Green Cells: Type in the "Search Bar" cell for a company name...
Orange Cells: If "COMPANY" is found, it displays the companies cell contents to the left.

What I want to do:

1. Click on a cell in any row
2. Have that cell realize what row it's in and navigate to the COMPANY column.
3. Copy the cells contents from the COMPANY column and replace the current contents of the "search bar" cell
4. VLOOKUP will then handle the rest.

---

Possible?

Is this possible with Google Sheet functions alone? Or is this something that should be handled with a script? How would I got about it?

Answer 1

There is currently no way to create an onClick function. The closest thing we have is an onEdit() function. I can think of two ways to make this sort of work.

1) This onEdit version works only if you don't mind locking your script down. You could write an IF statement to create a way to stop this from running temporarily while you edit it.
2) You could setup a second sheet and have the values imported from there or backed up there, and then just run this on the first one at all times.

function onEdit(e) {
//This sets up the worksheet variables
 var worksheet = SpreadsheetApp.getActive();
 var sheet = worksheet.getActiveSheet();
//This saves the original value e
 var origValue = e.oldValue
//This figures out where you clicked, and gets the K+rowyouclicked value.
 var selection = sheet.getActiveCell().getA1Notation();
 var splitselection = selection.match(/([A-Za-z]+)([0-9]+)/);
 var CompName = sheet.getRange("K" + splitselection[2]).getValue();
//set the value on the search bar and return the original value to where you changed.
 sheet.getRange("C3").setValue(CompName);
 sheet.getActiveCell().setValue(origValue)
}