dimanche 30 décembre 2018

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 take 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.

Aucun commentaire:

Enregistrer un commentaire