Convert to #REF! after Refreshing Results


We use BS results for our further analysis by using excel formula, like “Index & Match”, “V-lookup”.
But when BS feeds get Refreshed to a different period and numbers changes, the formula gets screwed and value referred to turns into “#REF!”.

How to avoid value in the cell (after refresh) to turn into “#REF!”?


What is BS, Olesya?

It might be that you need to look at using dynamic values/named ranges for the cells/ranges that get turned into #REF! when you refresh the feed

Hi Sayling,

I do not understand your comment. What exactly and how I can check it?
I use simple formula - v-lookup for Balance sheet (BS) reconciliation. Basically, I v-look up GL accounts on the TAB with Balance sheet (BS) to the next TAB with GL support. But when I change periods, v-look up search criterion turns into #REF!. How can I fix this up?
I set up multiple templates, wasting so much time and now after each refresh, the outcome becomes non sense! Is it a deficiency of DataDear??
I would appreciate if somebody from support team can contact me to discuss this matter since it is urgent.

It sounds as though the issue is with the formula you’re using, or rather the parameters used in your VLOOKUP.

Can you paste the formula here and I’ll try and help? I suspect we’ll need a few posts to resolve this, but happy to try and help if I can

Hi @olesya.gracheva and @sayling

Since the report is being pulled through without the “periods as rows” function, the columns are given a designation when pulled through. So when the period is changed, that designation also changes, so the formulas gets lost and shows a #Ref value.

It does not do this when using periods as rows because the columns do not change, so the designation remains the same.

The only solution we can offer is to pull through the data using the “Periods as rows” function.

Please let me know if you require any further clarification.

Kind regards,


1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.