how to resolve #ref! error

[summerhayashi]

I used excel to pull results of a sql query via odbc connection, e.g. columns
A,B,C. Thereafter, I add a formula at column D, =A2*B2 (row 1 is the header).
Upon data refresh, if the results return no rows, the formula from row2
onwards were hit with #ref! error.
ie.
at row 1, column D shows =A2*B2
from row2 onwards, columnn D shows =#ref!*#ref!
Can anyone tell me how to resolve this?
Thanks!

 

[Gary''s Student]

From what you describe, the refreshment of data is not like pasting new data
in cols A thru C.

I suggest that you put formulae in only the top of column D. Copy the
formulae down after each refresh.

 

[summerhayashi]

Hi, thanks for your suggestion, I have manage to find the solution through
another forum, ie to set the auto fill at the data range properties dialog
box.
thanks.