#### PhilipVerspreeuwen

##### New Member

- Joined
- Jul 14, 2014

- Messages
- 4

After searching for a solution for a few days now, I can't settle with my current workaround... All help is therefore appreciated.

Situation: I have an excel 2013 (pro) worksheet coupled to a data warehouse (SQL2005). In this excel, I created a datamodel with different calculated fields and columns. So far so good, everything works with no error as long as I am in excel.

Problem: When I open my excel and it loads the data for the first time, it errors on 2 calculated columns (different times):

*The query referenced calculated column 'X' which does not hold any data because evaluation of one of the rows caused an error.*

Dirty solution 1: When I discard these errors and refresh my data once my excel is open, no errors given and all calculated columns are ok. This is not a 'clean' solution... I would prefer that the calculated columns are ok when opening the excel and loading the data the first time.

Details:

The following calculated column is giving the error when opening the excel:

=IF([nummer]=MINX(FILTER(cynheadtel;[hoofdnummer]=EARLIER([Hoofdnummer]));[nummer]);RELATED(cynform[geleverdv])*RELATED(BI_Artikels[cu_per_vo]);0)

All rows in the column have value #ERROR

Dirty solution 2: I have 'wrapped' the formula in an IFERROR(

*formula*;0), this 'hides' the error messages so I just need to refresh after the intial load. Nevertheless, It would still be better without an aditional refresh. Instead of #ERROR, all rows now have the value '0'

I just can't figure out why a refresh solves my problem... If I need to give any additional input, just let me know. But at this moment I hope I can just rephrase the formula so it works without reload.