Reference links break in formula

I want a formula that can reference updates on another sheet and add the info onto my sheet. The other sheet is a schedule that has job locations and PTO time for each individual on each day of the week. I want to know the dates that each individual on the sheet has an upcoming PTO, and for my sheet to update with 'PTO' on those dates for those people (I do not need the other info/ job locations on my sheet, only PTO and PSL)

I have set up my sheet the same as the reference sheet (i.e. the left hand column has the date and days, each subsequent column has the names of each individual). I just need the PTO data to populate on my spreadsheet from the data on the other sheet.

Below is the formula I'm currently using, however, it constantly breaks and becomes 'invalid'. It will work for a short time, maybe a few hours or days, but inevitably, the reference to the other sheet breaks. How can I fix this issue? is there a better formula I can use to achieve this goal? thanks in advance!

=IF(OR(INDEX({Sterling}, MATCH(Date@row , {Specialist Schedule date}, 0)) = "PTO", INDEX({Sterling}, MATCH(Date@row , {Specialist Schedule date}, 0)) = "PSL"), INDEX({Sterling}, MATCH(Date@row , {Specialist Schedule date}, 0)), "")

note: 'Sterling' and 'Specialist Schedule date' are the links to the other sheet that keeps breaking/ becoming 'INVALID REF' on my spreadsheet.

Additional info: I have 'read only' access to the other sheet, if that makes a difference. And there are others contributing/updating the other sheet on a daily basis

Find more posts tagged with

Sheets

Formulas

Cell linking

Accepted answers

All comments

Jgorsich

I've used read only access in similar situations and not had a problem. The fact that your references themselves break sounds like something is being done to invalidate them by other users. Can you do a screenshot of the references?

casey.s

is this the screenshot that you're looking for? (i have only blacked out the job locations and sensitive data)

here is the formula in the first cell of 'Sterling' column on my spreadsheet:

Jgorsich

In that picture I can't tell which cells you are referencing. Is it an entire column, multiple columns, or just a subset? It ALMOST looks like you are referencing the whole sheet - which, if that is the case, is likely your problem. I've found multi-column ranges to be more fragile than individual column ranges and more easily broken.

casey.s

'Sterling' is referencing the entire column under that same name (just one column). Each column on my sheet is linked/ referencing the entire corresponding column on the other sheet with the same name.

… just as the 'Specialist Schedule date' column is referencing the entire 'date' column on the other sheet as well (this one is also breaking)

So the 'Sterling' column on the other sheet is being referenced on my sheet, under the 'sterling' column, and so on with each individual/column

Should I be doing this differently?

Jgorsich

That all sounds correct, and it doesn't seem like your references should be breaking from anything you are doing or have done (that I can see). Have you checked with the other editors to see what they are doing (or viewed the Activity Log to see what has been being done)? I was just trying to force an invalid reference (from a previously valid reference) on a couple of sample sheets and wasn't able to succeed.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.