Sorry for posting this again but I really need advice

  • Thread starter Thread starter gregork
  • Start date Start date
G

gregork

I have created an excel workbook that basically consists of one sheet for
recipe formulation and one sheet that is a database of raw
materials and their specifications. When I formulate a recipe on sheet 1, I
use numerous lookups to sheet 2 to formulate the right blend. I now have
decided that my database would be better maintained if it was on access so I
really need some definitive answers on the following questions:

1. If I have my raw materials database on access can my excel spreadsheet
still use lookups
based on the data cells in access?

2. If yes to above - will I have to re-write all my excel formulas or is
there a way around this?

3. What is the correct procedure for conversion. i.e. do I start with my
excel sheet and go >data>convert to ms access... or should I start in access
and go >file>get external data?

4. When I formulate a recipe in excel I also want certain information
(recipe No. , date, weight,etc) sent to an access database when I print or
hit an enter button. How do I do this?

Sorry to ask so many questions all at once but access is completely foreign
to me and I really don't want to waste time starting off on the wrong track.

Many thanks in advance to anyone offering advice.

gregork
 
Replies in line:
1. If I have my raw materials database on access can my excel spreadsheet
still use lookups
based on the data cells in access?
Click to expand...

The database equivalent of a worksheet is a table. Tables consist of
rows of columns rather than cells. You can look up data in a database
table from within Excel so the answer is 'yes' but probably not in the
way you mean...
2. If yes to above - will I have to re-write all my excel formulas or is
there a way around this?
Click to expand...

If the answer to the above is 'yes' then the answer to this one is
also yes. There are several ways to get data from a database into
Excel. I'm on record as favoring Excel getting the data by executing a
SQL SELECT query using ADO. It's not the easiest way but it gives me
greater control over the data.
3. What is the correct procedure for conversion. i.e. do I start with my
excel sheet and go >data>convert to ms access... or should I start in access
and go >file>get external data?
Click to expand...

Probably better to do an import from within MS Access.
4. When I formulate a recipe in excel I also want certain information
(recipe No. , date, weight,etc) sent to an access database when I print or
hit an enter button. How do I do this?
Click to expand...

I favor Excel executing an INSERT INTO..SELECT query using ADO.

--
 
Back
Top