#Ref error with refreshable dataset

[MahmoudSa]

I have a dataset retrieving data from SQL view, upon which I have built several functions. The problem is, when I refresh the data set, all the funtions get corrupted with the common "#Ref" error.


Your help is highly appreciated,

 

[MahmoudSa]

The data set changes, the original data set now has 7 columns for instance, when I refresh (after changing the data source), the new data set being retrieved would not necessary has 7 columns, it could be a different number of column. In my example above, the new data set brings back only 2 columns instead of 7
Therefore, the formula columns shift to the left.

I have tried to put the formulas in another work sheet, to make sure that the "formula" columns won't shift, Same result

 

[Andrew Poulsom]

And what is your setting in the link I posted?

 

[FDibbins]

Is this cross-posted here?
http://www.excelforum.com/excel-for...35521-ref-error-with-refreshable-dataset.html

 

[MahmoudSa]

Mr. Andrew
You have absolutely made my day.
You have pointed me to the precise direction, it was all related to the "Properties" of the connection string. I have done the following:
1- I have unchecked all the options related to "Data Formatting and Layout"
2- Second section (If the numbers of the rows in the data range changes upon refresh), > Overwrite existing cell with the new data.

The original formulas and function are performing as supposed to with no problem at all by now.

Isn't there a way to verify the correct answer in the forum ?

Thanks again Mr. Andrew