Hi Charles,

Yes... Excel can be SO frustrating when it tries to be helpful!!

I'll try both your Offset method (far easier as it avoids that dreaded
option... Operator Input!) and the macro tomorrow when I'm back in the office
& report back.

Thanks

"Charles Chickering" wrote:

Colin, while I'm no expert on query's, this does sound normal to me. It sound
like when the query updates it is moving the cells containing the resulting
data. Excel attempts to be intelligent (and fails miserably) by tracking the
cell that it was originally referenced to. While a macro would work, the
"Offset" command might work for you. I learned this trick from Dick Kusleika
at "Daily Dose of Excel"
http://www.dailydoseofexcel.com/

For your formula, instead of using the address of the cells in the query
data, use an "Offset" of the cell it resides in. For example this would be a
formula to sum columns "A" through "G" with the formula residing in column
"H":
=SUM(OFFSET(H1,0,-7):OFFSET(H1,0,-1))
Thus the only "refereneced" cell is the cell that the formula itself resides
in, the data can be moved, deleted etc. and the formula shouldn't break.

If you want to write a macro anyhow, this should get you started:

Sub WriteFormula()
Dim r As Range
Set r = Range("H1:H" & FindLastCell.Row)
r.Formula = "=YourFormula"
End Sub

Function FindLastCell() As Range
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range
If WorksheetFunction.CountA(Cells) 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
Set FindLastCell = Cells(LastRow, LastColumn)
Else
Set FindLastCell = Range("A1")
End If
End Function

Let me know if you need any more help.
--
Charles Chickering

"A good example is twice the value of good advice."


"Colin Foster" wrote:

Hi,

I have an Excel 2003 spreadsheet which retrieves the majority of its data
from a Query; the spreadsheet then needs to perform a number of calculations
on this data.

If I select the "Fill Down Formulas in columns adjacent to data" property,
then whilst the formulas fill the first time, when the query is refreshed
then the formulas stop working - some have a #REF value & others start
referencing, for example, Row 497 (which is where the previous "version" of
the query's results extended to).

So, two questions...

First of all, am I doing "something wrong" with the query parameters, or is
my error typical?

Secondly, as an alternative, I was going to create a macro to copy the
formulae down to the current last row, but I'm not sure of what code to use,
so any help would be very welcome.

Regards
Colin Foster
ps... Happy New Year!!