#Invalid Ref error in a report, but not the source

Jason_W

I'm seeing an invalid reference error in a report on a dashboard, but the source data is good and the vlookup is pulling in the data I want to see.

Any thoughts on the cause/solution? I've narrowed down, deleted and re-created the cross sheet reference several times and the error persists.

Screenshots of the error and data source are below.

Genevieve P.

Hi @Jason_W

I'm unable to re-create what you're seeing with a VLOOKUP; if the source data shows correctly then it should pull through to the Report. However I wonder if the reference range is getting too large to calculate.

Can you try swapping out your VLOOKUP formula for an INDEX(MATCH instead? This will evaluate the two columns separately without including any cells between them.

An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match}, 0))

Let me know if selecting two smaller ranges instead of one lookup table has helped get rid of this issue!

Cheers,

Genevieve

MGA

I am experiencing the same issue. All cells in a formula column display  "#INVALID REF". The formula resolves in the Sheet, but not in reports while either viewing the report directly or as displayed in a dashboard. The "Reference" sheet has 18K+ rows.

I have tried the VLOOKUP formula as well as and INDEX coupled with a MATCH with the same result.

My objective is to display the user name that is derived from their email address (Contact Column). I cannot figure out how to do this otherwise.

Genevieve P.

Hi @MGA

It sounds like the formula calculation is throwing a #Invalid Ref error message because it's hitting a timeout due to the complexity of the reference (size). INDEX(MATCH should help reduce the reference sizes, but if that still isn't working I would recommend using a smaller reference sheet, if possible.

MGA

Strangely enough, it was working even in reports displayed in dashboards. Now it is not again. Not sure what happened.

Regardless. Is it possible to have the column formula on trigger for a given row, when the row has been added or the column used to MATCH has been changed for that given row?