Updated the question; hope this is enough info. Please say if there is anything else needed to answer this question! I am new here, but I am more than happy to learn!
I am looking for a way to tell cells GN4:GN to only copy from cells in GM4:GM if they contain "PASS", "UNCERTAIN", or "REJECT"; if not, nothing should be done. It is important that if the cells in GM4:GM contain something else than the listed words, GN4:GN stay the same as before.
So I have got two sheets:
Sheet 1 is the dashboard, in cell B3 you can enter an email adress (that belongs to a candidate) and through vlookup you can see all the relevant data for this email adress. Now, you want to make a decision based on the data you see and it would be nice if that decision could be made on the present sheet. I created a dropdown menu with 'PASS', 'UNCERTAIN', and 'REJECT'.
Sheet 2 is the data sheet. Here, the decision made in sheet 1 should be saved --> to the correct e-mail adress (or row).
To solve this I made two columns GM & GN. GM contains the follwing:
=IF($A4=Sheet1!$B$3;VLOOKUP($A4&"dec";
{ARRAYFORMULA(Sheet1!$B$3&Sheet1!$C$6)
\Candidate_View!$E$6};2;0);"")
So this orders cells in GM to either give out what is in Sheet 1 or be left blank. And when I change the email adress in Sheet 1, another cell in GM will have input, and the rest will be blank.
In GN I thought about doing a Query, but I do not think it will work. The problem is, there are formulas that can tell you in which cells a certain "text" is but no formulas (that I know of) act only if a certain "text" is in a cell (like a trigger).
This is what I dream of:
Moment 1: Moment 2:
|A |GM |GN | |A|GM |GN |
|----------------------|---------|---------| |-|----|---------|
4|[email protected] | |UNCERTAIN| | |PASS|PASS |
5|[email protected] | |UNCERTAIN| | | |UNCERTAIN|
6|[email protected]|UNCERTAIN|UNCERTAIN| | | |UNCERTAIN|
7|[email protected] | |PASS | | | |PASS |
8|[email protected] | |PASS | | | |PASS |
Also tried out doing it in apps script but did not get that far...
function decisions(){
var source="GM4:GM";
var destination="GN4:GN";
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange(source).findAll["UNCERTAIN","PASS",
"REJECT",false] //so this is basically which cells I want to copy
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange(source).
copyTo(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange(destination) // and this
is where I want them copied. How do I combine the two?
}
Hope what I'm trying to do is a bit clearer now.. Only to assure you again. I am trying to figure out on my own as well, just thought someone might have a good idea or a clue for me. No need to solve the whole thing. I have started with google sheets about a months, so I am still looking for hours to find something like query...
Thank you!
I am looking for a way to tell cells GN4:GN to only copy from cells in GM4:GM if they contain "PASS", "UNCERTAIN", or "REJECT"; if not, nothing should be done. It is important that if the cells in GN4:GN contain something else than the listed words, GM4:GM stay the same as before., I cannot understand about your goal. I apologize for my poor English skill. In order to correctly understand about it, can you provide the sample input and output situation you expect? – Tanaike