excel formulas to Access

D

DP7

HI, I hope someone will be able to shed some light on my problem. I have
several HUGH spreadsheets; I have realized that to manage this properly I
need to my data over to an access database. My spreadsheet has quite a few
formulas from lookup tables, calculation to formulas that manipulate text
entered in the spread sheet. I would like to know is there any standard way
for me to “transfer†my excel formulas in the new access database. If there
is no standard what is best resource for me to consult to solve my issue.
THANKS IN ADVANCE
 
J

Jeff Boyce

Access is not a "big" spreadsheet. Even though an Access table might look
like a spreadsheet, it isn't one. It can't store formulas, just values.

Why have you decided that Access can do something you can't with a
spreadsheet? What is that?

If you've been working in Excel (or other spreadsheets), you've learned to
organize your data one way. Learning to organize your data in Access will
require a change-of-mind. Look into "normalization" before you start
designing your Access tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
S

Steven Chicago,Illinois

DP7,
If your spreadsheets are huge because of the amount of data (raw data,
not calculated data)(and your getting close to the max size allowed in
Excel), then yes import the raw data into tables in access (doing the proper
normalization that everyone is talking about). Then see if you can link a
Excel spreadsheet to the raw data and do your calculations in the
spreadsheets. The processing of calculations is sometimes a little different
between Excel and Access. So depending on how complex your
formulas/calculations are? But if you can re-create your calculations/results
to the penny/decimal accuracy that you desire in Access Reports/queries, it
could be more user friendly (for your end users) in Access with custom forms
to select the reports they want.
Alot depends on your skills in programming/logic/database
design(normalizing data)/report writing and will power to go to the next
level.

Also think about how you are entering the raw data into Excel, and if it
would be easier to enter it into a Access custom form or even a automated
import routine, as comapred to the Excel method your using?

Maybe you can find a local friend that has gone this direction before and
can help.

Good Luck with your Learning and Growing!
 
Top