Google-sheets – How to we make a field (Cell) value mandatory if another field (cell) have specific value, while submitting the data

formulasgoogle sheetsgoogle-sheets-data-validation

I am developing a small attendance app in Google Sheets. Each day after choosing their name or team, the supervisor will see a list of their employees to update their attendance status. The list will be populated according to the latest status of each employee, which will be updated if the supervisor changes their status as "Transferred" or if HR change their status/shift. I want the list to be populated after they change the optional cells at the second row, and then after updated, they need to press the Submit button to save the data. (Only records which are not "No Show" will be saved in a specific range).

My concern is to make the 'Transferred To' field mandatory for the employees whose status is 'Transfer'. I mean that this field cannot be empty when they press Submit. (A message box shows the issue and their status rolls back to 'NO SHOW'. Also if the status field is not Transfer, the 'Transferred to' field should be empty. 'Transferred to' should be a Dropdown list (Data Validation) which I think is an easy thing to do ;-).

I am not sure if it is just a case of adding dependent Data Validations or if there is a better solution for this as well? I would be highly grateful if anybody can guide me on this as I am a newbie in Google Sheets. But I am trying that by myself too.

Here is a link to a simplified format:
Sample Google Sheet

I am also thinking of taking such data by a user form, as I need a new group and a new shift.

This is an image from the actual Google sheet.

Best Answer

to color it based on Transfer in B column:
```
=REGEXMATCH(B4,"Transfer")
```

Related Solutions

Google-sheets – subtract a cell value in another cell that already have one in Google Spreadsheets

As Google Sheets does not natively support iteration with circular referencing, you will need to resort to Google Apps Script. Eg:

function onEdit(e)
{
  var editRange = e.range;
  var sheet = editRange.getSheet();
  if (sheet.getName() == "Sheet1" && editRange.getA1Notation() == "B1")
  {
    var updateRange = sheet.getRange("A1");
    var newValue = updateRange.getValue() - e.value;
    updateRange.setValue(newValue);
  }
}

Google Sheets – Sort Function Moves Data to Bottom

Cells containing the empty string "" appear empty but are not (isblank returns FALSE for them). They get sorted ahead of nonempty strings, which is not what you want. To avoid this, replace

if(isblank(B1:B), "", datevalue(B1:B))

by the simpler

iferror(datevalue(B1:B))

The command iferror returns its (optional) second argument if there is an error evaluating the first argument; otherwise it leaves the cell blank. Blank cells are sorted to the bottom.

A potential drawback of the above is that misformatted dates are simply ignored, while you may want to see and fix them. This is something you can address separately; e.g., have a column with

if(isblank(B1:B), 0, if(iserror(datevalue(B1:B), 1, 0))

and sum over it to get the total number of nonempty but invalid date cells.

Another potential issue is that you are sorting a range containing arrayformula. The sort may move the formula to another row, creating a mess. It's safer to exclude the header row from sorting. Replacing

var data = JobList.getDataRange();

var data = JobList.getDataRange().offset(1,0);

would do it.

Best Answer

Related Solutions

Google-sheets – subtract a cell value in another cell that already have one in Google Spreadsheets

Google Sheets – Sort Function Moves Data to Bottom

Related Topic