#Invalid Ref on a metrics sheet & report

Jeniese Chaffin

Hello, I created a data collection sheet in which end users enter information re: employee training compliance. I then created metrics sheets to capture the details for each employee location/site - basically counting the number of employees in each location that completed each training class. I then have a report that pulls this information by site onto a dashboard.

The following has happened twice now and am wondering if there is a way to fix this or troubleshoot. All of my metrics sheets, reports and dashboard worked as expected. Then I received an #Invalid Ref on the metrics despite the data collection tool being populated correctly. When I investigated the formulas on the metrics sheet, it looks like the link to the reference sheet has removed the link for each column (refer to the formula in the second snip below). If there any way to avoid this "unlinking" from the source reference? So time consuming to fix.......... :(

Thank you for your help!

Paul Newcome

I would double check the activity log on the source sheet to see if someone is accidentally removing the links. It can be very easily done in just a few very simple clicks on the source sheet so can easily happen if people aren't paying attention to where they are clicking or what they are clicking on.

Paul Newcome

I would double check the activity log on the source sheet to see if someone is accidentally removing the links. It can be very easily done in just a few very simple clicks on the source sheet so can easily happen if people aren't paying attention to where they are clicking or what they are clicking on.

Jeniese Chaffin

thank you, Paul! I have reviewed the activity log. Since the log does not directly say which formula was changed or deleted, I am wondering what activities would identify if a cross-sheet reference was edited. Would the notes in the picture below be what I am looking for when troubleshooting? Also is there any good way to avoid that (i.e. a more appropriate permission to give the end user or lock a column)?

thank you for your help!

Paul Newcome

This would be what you are looking for: