How to check spreadsheet cell value with MATCH without triggering NOW() function?
Trying to create a table that checks if the room key is already taken.
Google spreadsheet link.
An employee selects or enters the value into F2 cell. The formula in the G2 cell =IF(ISERROR(MATCH(F2,C4:C350,0)), "Brīvs", "Paņemts") tests whether the key is taken or not. The formula runs through C column and tests for the value.
While matching, it is also triggering the formula in B4 cell. Formula in B4 being =IF(C4>0, now(), "")
Issue is, every time an employee selects/enters the value, the MATCH function triggers the now() function and overrides the newest time if it matches the search criteria.
Is there a way of testing for the value without invoking the now() function so that the time stays as it was? Limiting recalculation counts in spreadsheet settings does not aid since an employee may not enter the value correctly from the 1st time.
Tried putting the value into another cell by "=" to nearby cell and =cell(contents, cell coordinate), but these refer back to the original values and Spreadsheet would recalculate all the references.
if-statement google-sheets match circular-reference
asked Dec 30 '18 at 17:16
Andrejs ZavaruhinsAndrejs Zavaruhins
2717
add a comment |
Trying to create a table that checks if the room key is already taken.
Google spreadsheet link.
An employee selects or enters the value into F2 cell. The formula in the G2 cell =IF(ISERROR(MATCH(F2,C4:C350,0)), "Brīvs", "Paņemts") tests whether the key is taken or not. The formula runs through C column and tests for the value.
While matching, it is also triggering the formula in B4 cell. Formula in B4 being =IF(C4>0, now(), "")
Issue is, every time an employee selects/enters the value, the MATCH function triggers the now() function and overrides the newest time if it matches the search criteria.
Is there a way of testing for the value without invoking the now() function so that the time stays as it was? Limiting recalculation counts in spreadsheet settings does not aid since an employee may not enter the value correctly from the 1st time.
Tried putting the value into another cell by "=" to nearby cell and =cell(contents, cell coordinate), but these refer back to the original values and Spreadsheet would recalculate all the references.
if-statement google-sheets match circular-reference
asked Dec 30 '18 at 17:16
Andrejs ZavaruhinsAndrejs Zavaruhins
2717
1
You may wish to look into using code to set the timestamp instead of an ever-changing formula NOW(). I'm not sure what your conditions for updating the timestamp are. You may wish to look at yagisanatode.com/2018/02/21/…
– s1c0j1
Dec 31 '18 at 15:10
add a comment |
Trying to create a table that checks if the room key is already taken.
Google spreadsheet link.
An employee selects or enters the value into F2 cell. The formula in the G2 cell =IF(ISERROR(MATCH(F2,C4:C350,0)), "Brīvs", "Paņemts") tests whether the key is taken or not. The formula runs through C column and tests for the value.
While matching, it is also triggering the formula in B4 cell. Formula in B4 being =IF(C4>0, now(), "")
Issue is, every time an employee selects/enters the value, the MATCH function triggers the now() function and overrides the newest time if it matches the search criteria.
Is there a way of testing for the value without invoking the now() function so that the time stays as it was? Limiting recalculation counts in spreadsheet settings does not aid since an employee may not enter the value correctly from the 1st time.
Tried putting the value into another cell by "=" to nearby cell and =cell(contents, cell coordinate), but these refer back to the original values and Spreadsheet would recalculate all the references.
if-statement google-sheets match circular-reference
asked Dec 30 '18 at 17:16
Andrejs ZavaruhinsAndrejs Zavaruhins
2717
Trying to create a table that checks if the room key is already taken.
Google spreadsheet link.
An employee selects or enters the value into F2 cell. The formula in the G2 cell =IF(ISERROR(MATCH(F2,C4:C350,0)), "Brīvs", "Paņemts") tests whether the key is taken or not. The formula runs through C column and tests for the value.
While matching, it is also triggering the formula in B4 cell. Formula in B4 being =IF(C4>0, now(), "")
Issue is, every time an employee selects/enters the value, the MATCH function triggers the now() function and overrides the newest time if it matches the search criteria.
Is there a way of testing for the value without invoking the now() function so that the time stays as it was? Limiting recalculation counts in spreadsheet settings does not aid since an employee may not enter the value correctly from the 1st time.
Tried putting the value into another cell by "=" to nearby cell and =cell(contents, cell coordinate), but these refer back to the original values and Spreadsheet would recalculate all the references.
if-statement google-sheets match circular-reference
if-statement google-sheets match circular-reference
asked Dec 30 '18 at 17:16
Andrejs ZavaruhinsAndrejs Zavaruhins
2717
asked Dec 30 '18 at 17:16
Andrejs ZavaruhinsAndrejs Zavaruhins
2717
edited Dec 31 '18 at 12:19
Andrejs Zavaruhins
asked Dec 30 '18 at 17:16
Andrejs ZavaruhinsAndrejs Zavaruhins
2717
asked Dec 30 '18 at 17:16
Andrejs ZavaruhinsAndrejs Zavaruhins
2717
asked Dec 30 '18 at 17:16
Andrejs ZavaruhinsAndrejs Zavaruhins
2717
2717
1
You may wish to look into using code to set the timestamp instead of an ever-changing formula NOW(). I'm not sure what your conditions for updating the timestamp are. You may wish to look at yagisanatode.com/2018/02/21/…
– s1c0j1
Dec 31 '18 at 15:10
add a comment |
1
You may wish to look into using code to set the timestamp instead of an ever-changing formula NOW(). I'm not sure what your conditions for updating the timestamp are. You may wish to look at yagisanatode.com/2018/02/21/…
– s1c0j1
Dec 31 '18 at 15:10
1
1
You may wish to look into using code to set the timestamp instead of an ever-changing formula NOW(). I'm not sure what your conditions for updating the timestamp are. You may wish to look at yagisanatode.com/2018/02/21/…
– s1c0j1
Dec 31 '18 at 15:10
You may wish to look into using code to set the timestamp instead of an ever-changing formula NOW(). I'm not sure what your conditions for updating the timestamp are. You may wish to look at yagisanatode.com/2018/02/21/…
– s1c0j1
Dec 31 '18 at 15:10
add a comment |
1 Answer
1
active
oldest
votes
Thanks to cOde for providing the hint.
The code below is modified to the following needs. Since the sheets will be subsequently added, the SHEETNAME variable is taken out. I have also added the isBlank option at the end, so that if there is no value in the cell, the timestamp is erased.
/**
* Creates a Date Stamp if a column is edited.
*/
//CORE VARIABLES
// The column you want to check if something is entered.
var COLUMNTOCHECK = 3;
// Where you want the date time stamp offset from the input location. [row, column]
var DATETIMELOCATION = [0,-1];
function onEdit(e) {
SpreadsheetApp.getActiveSpreadsheet();
var ss= SpreadsheetApp.getActiveSheet();
var selectedCell = ss.getActiveCell();
//checks the column to ensure it is on the one we want to cause the date to appear.
if( selectedCell.getColumn() == COLUMNTOCHECK) {
var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
dateTimeCell.setValue(new Date());
}
if (selectedCell.isBlank()) {
dateTimeCell.setValue("");
}
}
answered Jan 1 at 20:06
Andrejs ZavaruhinsAndrejs Zavaruhins
2717
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53979759%2fhow-to-check-spreadsheet-cell-value-with-match-without-triggering-now-function%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks to cOde for providing the hint.
The code below is modified to the following needs. Since the sheets will be subsequently added, the SHEETNAME variable is taken out. I have also added the isBlank option at the end, so that if there is no value in the cell, the timestamp is erased.
/**
* Creates a Date Stamp if a column is edited.
*/
//CORE VARIABLES
// The column you want to check if something is entered.
var COLUMNTOCHECK = 3;
// Where you want the date time stamp offset from the input location. [row, column]
var DATETIMELOCATION = [0,-1];
function onEdit(e) {
SpreadsheetApp.getActiveSpreadsheet();
var ss= SpreadsheetApp.getActiveSheet();
var selectedCell = ss.getActiveCell();
//checks the column to ensure it is on the one we want to cause the date to appear.
if( selectedCell.getColumn() == COLUMNTOCHECK) {
var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
dateTimeCell.setValue(new Date());
}
if (selectedCell.isBlank()) {
dateTimeCell.setValue("");
}
}
answered Jan 1 at 20:06
Andrejs ZavaruhinsAndrejs Zavaruhins
2717
add a comment |
Thanks to cOde for providing the hint.
The code below is modified to the following needs. Since the sheets will be subsequently added, the SHEETNAME variable is taken out. I have also added the isBlank option at the end, so that if there is no value in the cell, the timestamp is erased.
/**
* Creates a Date Stamp if a column is edited.
*/
//CORE VARIABLES
// The column you want to check if something is entered.
var COLUMNTOCHECK = 3;
// Where you want the date time stamp offset from the input location. [row, column]
var DATETIMELOCATION = [0,-1];
function onEdit(e) {
SpreadsheetApp.getActiveSpreadsheet();
var ss= SpreadsheetApp.getActiveSheet();
var selectedCell = ss.getActiveCell();
//checks the column to ensure it is on the one we want to cause the date to appear.
if( selectedCell.getColumn() == COLUMNTOCHECK) {
var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
dateTimeCell.setValue(new Date());
}
if (selectedCell.isBlank()) {
dateTimeCell.setValue("");
}
}
answered Jan 1 at 20:06
Andrejs ZavaruhinsAndrejs Zavaruhins
2717
add a comment |
Thanks to cOde for providing the hint.
The code below is modified to the following needs. Since the sheets will be subsequently added, the SHEETNAME variable is taken out. I have also added the isBlank option at the end, so that if there is no value in the cell, the timestamp is erased.
/**
* Creates a Date Stamp if a column is edited.
*/
//CORE VARIABLES
// The column you want to check if something is entered.
var COLUMNTOCHECK = 3;
// Where you want the date time stamp offset from the input location. [row, column]
var DATETIMELOCATION = [0,-1];
function onEdit(e) {
SpreadsheetApp.getActiveSpreadsheet();
var ss= SpreadsheetApp.getActiveSheet();
var selectedCell = ss.getActiveCell();
//checks the column to ensure it is on the one we want to cause the date to appear.
if( selectedCell.getColumn() == COLUMNTOCHECK) {
var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
dateTimeCell.setValue(new Date());
}
if (selectedCell.isBlank()) {
dateTimeCell.setValue("");
}
}
answered Jan 1 at 20:06
Andrejs ZavaruhinsAndrejs Zavaruhins
2717
Thanks to cOde for providing the hint.
The code below is modified to the following needs. Since the sheets will be subsequently added, the SHEETNAME variable is taken out. I have also added the isBlank option at the end, so that if there is no value in the cell, the timestamp is erased.
/**
* Creates a Date Stamp if a column is edited.
*/
//CORE VARIABLES
// The column you want to check if something is entered.
var COLUMNTOCHECK = 3;
// Where you want the date time stamp offset from the input location. [row, column]
var DATETIMELOCATION = [0,-1];
function onEdit(e) {
SpreadsheetApp.getActiveSpreadsheet();
var ss= SpreadsheetApp.getActiveSheet();
var selectedCell = ss.getActiveCell();
//checks the column to ensure it is on the one we want to cause the date to appear.
if( selectedCell.getColumn() == COLUMNTOCHECK) {
var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
dateTimeCell.setValue(new Date());
}
if (selectedCell.isBlank()) {
dateTimeCell.setValue("");
}
}
answered Jan 1 at 20:06
Andrejs ZavaruhinsAndrejs Zavaruhins
2717
answered Jan 1 at 20:06
Andrejs ZavaruhinsAndrejs Zavaruhins
2717
answered Jan 1 at 20:06
Andrejs ZavaruhinsAndrejs Zavaruhins
2717
answered Jan 1 at 20:06
Andrejs ZavaruhinsAndrejs Zavaruhins
2717
2717
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53979759%2fhow-to-check-spreadsheet-cell-value-with-match-without-triggering-now-function%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
You may wish to look into using code to set the timestamp instead of an ever-changing formula NOW(). I'm not sure what your conditions for updating the timestamp are. You may wish to look at yagisanatode.com/2018/02/21/…
– s1c0j1
Dec 31 '18 at 15:10