I agree as far as the human intervention and originally
the entire project was going to be in Access. However, as
with everything involving corporate america, they weren't
willing to pay for the extra work related to the project.
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 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.
-----Original Message-----
Hi Ted,
This is an area I'm not really familiar with, but my first thought is
that the worksheet is locked because it is in use by your linked table.
You should be able to update cell values by updating the corresponding
record and field in the linked table, but as far as I know there's no
way of accessing a formula (as opposed to its result) via a linked
table.
I'm worried about the design of your application, however. On the
database side, Excel workbooks don't make very good data stores (far too
easily screwed up by human intervention, for one thing). And there can
be problems with linked tables, especially if the columns contain mixed
data types or formulas.
On the Excel side, it seems that you have to routinely change the
formulas every time the data changes, another weak point.
Would it be worth restructuring the application to store its data in
Access? You could either do the calculations there too, or else automate
Excel to put the necessary data into cells in an Excel template for the
final output.
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!!
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
