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.

Find more posts tagged with

Sheets

Formulas

Reports

Comments

Daniel Stein

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

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?

Craig

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.

caroline_allen37286

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

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.

isaron

I'm experiencing this issue same error now. Has anyone found another solution?

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
Sheet Summary Formula
I am looking to count how many letters we have issued within each month, this is by going by the modified date, these are the formulas i have tried as i want to start this from August: =COUNTIFS(Modified:Modified, MONTH(@cell ) = 8, Modified:Modified, YEAR(@cell ) = 2025) However, it comes up with an error. Any ideas…