VLOOKUP to another sheet - cannot pull these fields into a report, getting #ERROR


I am using VLOOKUP in a sheet (call it Sheet2) to pull data from anotther sheet (Sheet1). Then, I'm creating a report that pulls data from Sheet2. This report is pulling some of the data from these VLOOKUP columns.

I'm seeing an issue where the report can only successfully pull data in sometimes. Sometimes I open the report and my data is there - which is great! However, sometimes I open it and I just see #ERROR in all of the columns that are using the VLOOKUP to another sheet. I can't give this to my team as a usable tool if it only works half of the time. 

Is this a known bug/issue that's being worked on?

If I can't pull those cross-sheet VLOOKUP fields into my reports, it really renders the cross-sheet VLOOKUP functionality useless for me since reports are the primary way my team interacts with the SmartSheet data. Hoping there's a solution for this. 




  • Daniel Stein
    Daniel Stein Employee
    edited 02/11/18

    Thank you Caroline for reporting. There is currently a known issue where cross-sheet formulas in reports can temporarily show #ERROR after editing the report. A fix for this issue is rolling out in the next few days.

    In the meantime, opening the sheet containing the formulas should make the errors in the report go away.

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    I was hoping this was going to be in yesterday's (2018-02-10) release.

    Is #ERROR also a general "something bad happened" in addition to the Contact List formula error discussed in the documentation?


  • Daniel Stein

    This fix is currently scheduled to roll out tomorrow evening (Tuesday). 

    Thanks for the question about #ERROR -- yes that can also be a general error. We'll look into getting that documentation updated.

  • AH-MAZING. Thank you!!! 

  • Ariel Lasry

    This error still occurs, but is also still solved by the solution above where you need to oppen the sheets being reported on that contain the cross sheet formulas.

  • Rafael Santiago

    This error still occurs!

  • Ian Ix
    Ian Ix ✭✭✭

    I'm getting a similar error still, but it occurs even without cross-sheet references. Specifically, I have a summary field which uses an INDEX/MATCH pair to pull the timeline variance of a project target:

    =-INDEX(Variance:Variance, MATCH(true, [Project Target]:[Project Target]))

    The formula works perfectly in when viewed in the sheet, but returns #INVALID OPERATION when pulled into a report. My thought is that this is an error with the new baseline functionality, as I am able to use the same formula above to pull from columns other than "Variance" without issue.

    Any help on this would be appreciated.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!