excel formulas to Access

[Guest]

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

 

[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

 

[jahoobob via AccessMonster.com]

You will transfer the spreadsheets to tables and only data will be
transferred, not formulae. If you have 4 in A1, 2 in B1, and =B1^A1 in C1
you will transfer 4,2,16. You will probably not want to transfer the
calculated fields into the tables.
Arithmetic operations are the same in Access and Excel. A number of
functions and conditions are the same e.g. You would write =sin(A1) in a
Excel cell and SineofField1:SIN(Field1) in a field in an Access query.
One of the differences is you use If() in Excel and IIf() in Access
You can type in function in Access help and one of your choices will be
Functions(by category) to look at the Access functions.

 

[Joseph Meehan]

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

Congratulations you are about to embark on a new way of thinking.

Databases work different than spreadsheets. Sort of like a toaster
works different from a frying pan, but they can both toast bread.

Be glad you asked before getting too far. Doing it right now will save
you a lot of grief later.

Study up on "NORMALIZATION OF DATA"

Take a good look at the sample database (called Northwind?) that comes
with Access. Get a book on Access. Figure out how you want to data stored
in Access in a normalized form before doing anything else.

We will be here for questions, but we don't know much about your data so
we can't be very specific yet and you don't know enough about normalization
to ask the questions yet.

Good Luck and hope to see you soon.

 

[Guest]

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!