CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
McChief
McChief

Reputation: 425

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

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.

Upvotes: 0

Views: 456

Answers (1)

Tedinoz
Tedinoz

Reputation: 8034

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.

Upvotes: 2

Related Questions