Access and updating linked Excel spreadsheets

[JD5241]

I have an Access 2000 database linked to an Excel spreadsheet. I created a data entry form in Access to update both the database and the linked spreadsheet.
In the spreadsheet, I have some formulas that make some calculations based on two numbers input with the Access form (calculates total time based on start and end times that are entered).
I have the formulas filled down the spreadsheet, even if there aren't any entries in the rows they are resting in (other than the formulas, of course). When I input data with the Access form, it puts the new row at the bottom of the spreadsheet, below where the formulas are filled down to.

Can a linked spreadsheet be updated over a row that has a formula in one (or more) of the cells so that the formula picks up the data and processes it accordingly? Or is it always going to put the data into the first empty row on the spreadsheet?

"It's a dog-eat-dog world, and I'm wearing Milk Bone underwear." - Norm

 

[CharlieT302]

The spreadsheet will be treated as a datasheet, which means that a new record will always be placed at the bottom of the existing rows.

Try inserting empty rows at the top of the spreadsheet and insert your formulas there, or place the formulas on another spreadsheet (you have 256 spreadsheets for a reason).

Also, be sure that the spreadsheet is not opened when the new data is entered using Access. If both are opened, and the spreadsheet is not saved before being closed, the data will be lost. However, if the spreadsheet is not open when the new data is entered, it will save automatically.

 

[JD5241]

I've decided to just let each row be entered at the bottom and not worry about any formulas, I can calculate those down the line further (Crystal Reports). As long as the data that matters is captured.

Thanks for your help!

"It's a dog-eat-dog world, and I'm wearing Milk Bone underwear." - Norm