How do you remove "#REF!" in one sheet linking formula if a cell in another sheet is removed?

0
votes

I have the following Google Sheet.

Sample

I have an Apps Script code called onEdit(e). When you enter a date into column B of the Qualifications sheet, the Employee is cut and pasted to into Qualifications - Employees Left sheet. That works perfectly but it creates a #REF! issue in the sheet called FormRanger and which is used to form my dropdown in Google Forms and in the other sheets.

The Employees name needs to remain in the spreadsheet so that it can populate the Qualifications - Employees Left sheet for future reference and in case the employee returns. I, however, want the employee's name (now #REF!) removed from the FormRanger sheet.

Would really appreciate some help with the code.

1
google-apps-scriptgoogle-sheets
What is the purpose of "Sheet6"? – Tedinoz
A way of sorting the names for the Qualifications sheet. I'd welcome a better suggestion – McChief
I'd extend the the onedit to re-build the named from qualifications. But what i was getting at earlier was the names on qualifications are sourced from Sheet6, so why not make sheet6 the validation source – Tedinoz
Okay - not sure how to do that – McChief

1 Answers

2
votes

You want Columns A and B of sheet "FormRanger" to always reflect the active employees. At present, this is done by assigning values to each cell such as =Qualifications!A5 but when an employee is deleted, this leaves #REF on "FormRanger".

An alternative is to make the values in Columns A and B dynamic by using a QUERY command.

=query(Qualifications!A5:D,"select A, D",0)

To use the formula

  • delete the entire contents of cells in Columns A and B or "Form Ranger" (1000 rows).

  • insert the formula in cell A2 of "FormRanger"

    The formula will populate both columns A and B, and will also fill down the appropriate number of rows.

    When an employee is deleted, the values will automatically adjust.