VLOOKUP with reference to another sheet fails randomly, empty columns (and filter issue)

João Lopes

Hi,

I have a sheet with a few cells that uses vlookup to another sheet. At random times, the vlookup doesn't fail per se, it just updates the cell values to empty/blank. If I refresh the page, the values will reappear. But having to refresh the page every time this happens is very annoying.

On top of that, I have a filter based on this column, even after refreshing and the values are back, I need to disable the filter and re-enable it in order for the rows to be properly filtered.

The only solution I thought to resolve this, was to create a "local" copy in a separate column. But I can't create a workflow to copy another cell value, and it doesn't appear to also have a trigger to "not blank".

Any thoughts on how to resolve this? Why can't the workflows take a formula in each field (i.e. conditional to control when it is triggered, formula-based value to assign to the cell, etc.)

Best regards

Paul Newcome

I suggest replacing the VLOOKUP with INDEX/MATCH. It is much more reliable, harder to break, more flexible, and requires less back-end resources.

João Lopes

Hi Paul, I did but I'm getting the same result, when I keep the page open for a while (the whole day), after a few hours the page auto-refreshes or something and I get empty cells, no error, no value, just blank.

When I manually refresh the page, I get the values but the filter is applied before the values are retrieved, meaning I need to turn off the filter and then turn it on again.

This is a status sheet, so I consult it for my team regularly throughout the day so doing the steps above is quite annoying. That's why I was trying to duplicate the column and have a local one and have the filter use the "local" column instead of the cross-reference one