3

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.

enter image description here

This is an image from the actual Google sheet.

Actual Sheet which I am thinking of

Improve this question
7
  • I want the blue cells in above picture, became mandatory and just can accept the values is available in Team column. Also the white Cells in Transferred to Column should be empty.
    – Mahhdy
    Commented May 13, 2019 at 14:41
  • What doy you mean by mandatory, in other words, what should happen if they aren't filled-up and when should that happen? By the way, the drop-downs are an option of data-validation not of conditional-formatting.
    – Rubén - Volunteer Moderator - ♦
    Commented May 13, 2019 at 15:55
  • Hi Ruben, The Google Sheet Hero ;-). Mandatory means if they don't fill that Cell, they Transfer entry didn't get through. For instance After choosing the Transfer a message box asks them to fill new cell, and if they don't do that, the Transfer entry rolls back to "No Show". I also was thinking of a userform type thing so after they chose Trasfer or Late Transfer, a form pops us and asks them New team and New shift and then write it to the Transfer To Column. But Yes The conditional Formatting was my bad, I meant Data Validation.
    – Mahhdy
    Commented May 13, 2019 at 16:27
  • Or the validation stage (For being Mandatory) can happen on Submit. I am adding a submit button to the form. I think it would be easier.
    – Mahhdy
    Commented May 13, 2019 at 16:34
  • 1
    Thanks for your nice words. Please don't forget to include the @ before a username when two or more users, besides the OP are participating to send a notification. Please add all the important details to the question. By the way adding a "submit" button could help to clarify help users understand what and when is happening :)
    – Rubén - Volunteer Moderator - ♦
    Commented May 13, 2019 at 16:37

2 Answers 2

Reset to default
1

  • to color it based on Transfer in B column:

    =REGEXMATCH(B4,"Transfer")

0

Improve this answer
3
  • 1
    No, Please read my post. I just make those cells blue to show which cells I am talking about to be mandtory. I need App Script Code for sure, as I need two condition to be matched. I don't want change of color. I want the blue cells became 1) Mandatory, so the user can't leave them empty, 2) The user just can choose among specific items, same with the ones is available in Team Column.
    – Mahhdy
    Commented May 13, 2019 at 15:09
  • I think I need a dependent dropdown coding for that. I am working on one of them which will post soon for help.
    – Mahhdy
    Commented May 13, 2019 at 15:15
  • I can use it as a reminder for users. This also could be beneficial.
    – Mahhdy
    Commented May 13, 2019 at 17:27
1

Google Sheets data validation only helps to prevent wrong in-cell data entering. You could use a custom formula to handle conditions but

  1. any editor could remove the data validation
  2. it could not directly prevent other actions, like disallow the "form submission".

As you already using buttons we could assume that you are using Google Apps Script. You could use it if the "submit" task should be completed or not. As Google Apps Script is based on JavaScript, there are many ways to do this, may the most common is to use an if statement with the appropriate condition

More about in-cell data validation (drop-downs)

The "drown down list" is only available for list or range options for valid entries, not for custom formula but you could use an auxiliary column to put there the formula that will calculate which are the valid data entries.

Anyway, in-cell data validation will not prevent the submit action and it will only show the error message when entering invalidad data. If the data was already there it will show a orange triangle.

Considering the above, I think that you will end by using Google Apps Script to implement the data validation that you are looking for.

Improve this answer
3
  • Yes, make sense I can do a check on each row. So Simple.... Thanks for the note. I am really confused in this new app now, I just had experience in VBA but now should deal with different issues and new syntax's.
    – Mahhdy
    Commented May 13, 2019 at 17:24
  • What I was actually looking for was a dependent drop down list guidance or example in App Script. But I will figure it out myself, no worry.
    – Mahhdy
    Commented May 13, 2019 at 17:26
  • 1
    @Mahhdy I extended a bit my answer
    – Rubén - Volunteer Moderator - ♦
    Commented May 13, 2019 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.