Issues with Cross Reference and VLookup

I have been trying for several days to get cross referencing to work, it is stuck in the processing status is what I have noticed. I even tried to use different browsers to see if that was an issue.

In order to provide data for testing, I cut down my files to remove sensitive data and just included the necessary data, now the cross references show active however the formula I successfully used in other sheets does not work in my new sheet after I updated the cross references.

Question 1: How long does it normally take for cross referencing to process in a larger file? I waited 24 hours and it still has not processed, last time I did this in the same file it was instantly.

Question 2: Why is this formula not working in my new sheet?

=IFERROR(INDEX({TEST of Activity Report Range 1}, MATCH(SoldTo@row, {TEST of Activity Report Range 2}, 0)), "")

{TEST of Activity Report Range 1} is Last Reported

{TEST of Activity Report Range 2} is SoldTo

*Note: this formula works flawlessly in my working file to bring in 3 columns of contacts (different cross references to different contact files). < These cross references were created back in Sept. Has something broken in Smartsheet since that time period or is it something I am doing wrong?

Question 3: Why is this Vlookup formula not bring in a match when there is obviously one there? See snapshot below

=VLOOKUP(SoldTo@row, {TEST of Activity Report Range 3}, 2, 0)

{TEST of Activity Report Range 3} is the range of columns SoldTo and Last Reported, and 2 is trying to pull in column Last Reported.

Any help will be greatly appreciated.

TEST File for Cross Reference.xlsx

TEST of Activity Report.xlsx

Find more posts tagged with

Formulas

Support

functionality

Cell linking

Accepted answers

Sameer Karkhanis

Couple of things you should check:

Data in the SoldTo column -- Interestingly, when I imported the attached TEST File for Cross Reference.xlsx, I noticed that the values displayed in the Smartsheet cells appeared correctly, however when I double clicked the cell there was ' before the value (Applied to values in all rows in the SoldTo column). Not sure how you created the data in that sheet, but please do check.
Make sure that the "Index to Activity" and "Vlookup" columns properties are of Date type
Replace 0 with false in your formulae (both the in the MATCH and VLOOKUP functions)

With all of the above addressed, everything worked for me.

On Question 1 -- There is no delay it is instant as you are cross-referencing a sheet in the formula and there is nothing to sync or so.

Hope this helps.

All comments

Sameer Karkhanis

Couple of things you should check:

Data in the SoldTo column -- Interestingly, when I imported the attached TEST File for Cross Reference.xlsx, I noticed that the values displayed in the Smartsheet cells appeared correctly, however when I double clicked the cell there was ' before the value (Applied to values in all rows in the SoldTo column). Not sure how you created the data in that sheet, but please do check.
Make sure that the "Index to Activity" and "Vlookup" columns properties are of Date type
Replace 0 with false in your formulae (both the in the MATCH and VLOOKUP functions)

With all of the above addressed, everything worked for me.

On Question 1 -- There is no delay it is instant as you are cross-referencing a sheet in the formula and there is nothing to sync or so.

Hope this helps.

MMiles

Thank you Sameer, this was driving me crazy...this whole time the culprit was the hidden ' before the value.

Sameer Karkhanis

Kool, glad the findings helped!

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.