Access data refresh changes ranges in formulas

I have a spreadsheet linked to an access database. On one worksheet I have some formulas that include ranges to calculate some of the data from the import.

However, each time I refresh the data, the upper cell named in the range within the formula has increased. E.g. $A$2:$A$10000 could read $A$2:$A$10020 at next refresh.

Is this typical of a link to access? I set all ranges from 2:10000 to take into account growth in the number of records in the database. Have I then, set my ranges up wrong? If so, how should I do it?

Also, why do I keep getting errors if a record is deleted in the access database?

Perhaps try using named ranges in your formulas. Make the named ranges dynamic using the offset formula in the "Refers To" box. This automatically adjust the height of your range to equal the number of rows that have data. The basic offset formula for a dynamic named range is

=Offset($A$1,0,0,CountA($A:$A),1)

$A$1 refers to the starting cell of your data column. If your data has a header row and your actual data starts in row two use $A$2

CountA($A:$A) sets the height of your range by counting the number of rows in Column A that contain data. Again, if your data has a header row, change the formula to CountA($A:$A)-1

This formula only works properly is the column referenced in the CountA formula does not have any blanks.

A search for Dynamic Named Ranged should give you plenty of more detailed walkthroughs.

Originally Posted by mo4391

...

This formula only works properly is the column referenced in the CountA formula does not have any blanks.

...

Unfortunately, this is where using dynamic named range would fall flat because there will be blanks from the Access import

Will all columns have blanks? Your data in Access likely has at least one field that doesn't allow blanks, either a primary key, or at the very least, a record count field. Include that field in your export from Access. Then just use that column for the address in the CountA formula. The column used in the CountA formula doesn't have to be the same column of your named range.