Excel: database query formula fill problem

[bmc1234]

I am importing data from an access database into columns A through G. Then, columns H through U all have formulas that are based on the data in A through G, in the normal way where formulas in row X are based on data in row X. The problem is that when data is added to the database and the next time the excel spreadsheet it opened and the data fills, the formulas in any lines that weren't used before match up to the wrong rows of data. For instance, a formula in row 5 should be based on data in row 5, but if 3 more rows are added the next time it's opened, that formula in row 5 is referencing row 8 and every row after that is off. 6 references 9, etc. The only column of formulas that works is the one just to the right of the query, in column H. Does anybody know why this is happening? In the settings for database query I had "fill down formulas in columns adjacent to data" selected.

Sorry if this post seems a little confusing. I'll clarify anything if necessary. Thanks.

 

[SkipVought]

Hi,

I don't understand. If you have selected fill down formulas in columns adjacent to data, then that's what should happen.

Can you post the formula (in col H) that IS working and any formula that is NOT working.

It almost seems as if you have some ABSOLUTE references in your formulas.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[bmc1234]

The formula for H2, the first row after the headers is

=IF(NOT(A2=""),IF(AND(NOT(B2="shpd"),NOT(B2="complete")),DATEDIF(A2,TODAY(),"d"),DATEDIF(A2,F2,"d")),"")

That column works and does fill down accurately, I think because it is the column right next to the query.
Here is the formula for J2, which doesn't fill properly, but still works if I go back afterwards and manually delete the inaccurate formulas and replace it with the right ones, referencing the right rows.

=IF(MONTH($A2)=1,$H2,"")

What would happen is J2 would look like this, or some other row other than the right one:

=IF(MONTH($A5)=1,$H5,"")

 

[SkipVought]

What formula is in COLUMN I????????????

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[bmc1234]

column I is blank, it's used as a spacer. Do you think that's whats messing it up?

 

[SkipVought]

fill down formulas in columns [red]adjacent[/red] to data

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[bmc1234]

Thanks. As I typed my previous response I realized that that's probably the problem. I've been working on it so long and just got used to that blank column and really completely forgot that that might affect things. Thanks for your amazingly fast responses.

 

[SkipVought]

You ought to AVOID empty columns and rows like the plague!

It makes table processing a bit more difficult!

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}