Unable to update field in a linked table

[Ted]

I have a database that is entirely dependant on an Excel
workbook and 14 worksheets through linked tables. The
spreadsheet is used to forecast needed 'employees' on a
given date. It uses the actual data that is entered in
the access form to make this calculation. Example: On
4/19 there were 36 employees on 4/20 it is forecasted that
there is a need of 35 employees. So, cell B2 contains the
value 36, while cell B3 contains a formula. As a person
enters data the formulas should be overwritten for each
day's actual data. My form shows the values and the
worksheet is doing its job. Problem: Access will not
allow the information in "cell B3" to be overwritten -
error message "unable to update field" the help says that
it is 'in use' or 'locked' neither is the case.

Please Help!!

 

[onedaywhen]

I have a database that is entirely dependant on an Excel
workbook and 14 worksheets through linked tables.

Heads up: in this ng you risk being told your database is incorrectly
designed and be advised to read up on you read up on Relational
Database Design Theory.

cell B2 contains the
value 36, while cell B3 contains a formula. As a person
enters data the formulas should be overwritten for each
day's actual data.

Are you sure this is what's happening? It sounds likely B2 is a
dependent of the formula in B3, so the formula remains the same and
merely shows a different value when e.g. the worksheet is
recalculated.

Problem: Access will not
allow the information in "cell B3" to be overwritten -
error message "unable to update field" the help says that
it is 'in use' or 'locked' neither is the case.

You can't change a formula cell with a query. In order for a
workbook's formulas to calculate properly and efficiently, Excel
maintains trees and chains of dependencies. If you were allowed to
change a formula it would upset the dependencies. You can only change
a cell formula by opening the workbook in the native Excel app e.g.
using automation from MS Access.

--

 

[Ted]

The whole thing was done only in Excel in the first place
and human error was a very big factor. There are about 75
cells of data that have to be entered by hand daily into
11 of the 14 spreadsheets, by using Access I keep the
person entering data out of Excel. In this way, the only
time Excel is ever opened is to view or print data.

**The formula is cell B3 grabs data from another cell and
calculates a 'forecasted' value. The same formula was in
B2 until it was updated with the actual value. As would
be the same with B3 and so on as the days go on.**

The data entered in Access actually goes into the other 3
spreadsheets and then the 11 link to the other
spreadsheets containing whatever data it needs to
acquire. Every other aspect works seamlessly.

The only problem was the one listed below. Getting the
result of the formula - no problem - just needed to
overwrite the formula. However, I did provide a 'work
around' for the time being - but it does require someone
going into the spreadsheet.

A solution I believe could be the following and I will try
it:

Create another worksheet that contains the formulas that
manipulate the data for forecasting, then remove all
formulas from the worksheet that Access deals with - that
allows Access to update the cells. Then the 11 worksheets
that actually grab the data will need to have their
formulas changed to 'If' statements checking to see if
there is a value in the sheet Access uses, if so use it,
if it doesn't, grab the data from the new sheet
that 'forecasts' the information with formulas -
seperating the two. Although very convoluted, it should
work.