DATABASE pulling values from Spreadsheet

I know just enough to be dangerous.

We have our business system set up to pull database queries out of the 
system, say for example, raw material part numbers, description and average 
cost or last cost as well as avg usage.

I would like to set up an Access database to contain recipies (plastic 
blends for different applications)

The current cost tool that sales has requires historical cost data from the 
system in order to cost products.  Small runs tend to inflate cost 
information (lots of down time, poor yields)

The database would have each ingredient, amount, cost and would look up the 
values from one of these queries (spreadsheet) that updates with actual cost 
information from the system each time the file is opened up (refresh upon 
opening)
Thats my problem- how to populate a field in Access table with a value 
looked up in an external spreadsheet. (lookup by raw material part number- a 
six digit number)

Thanks
Paul 


0
Anon
2/26/2008 11:28:15 PM
access 16762 articles. 3 followers. Follow

3 Replies
1299 Views

Similar Articles

[PageSpeed] 32

On Tue, 26 Feb 2008 15:28:15 -0800, "Anon" <anon@onandon.com> wrote:

>Thats my problem- how to populate a field in Access table with a value 
>looked up in an external spreadsheet. 

File... Get External Data... Link to link to the spreadsheet; create a query
Joining the linked spreadsheet to your query by the six-digit (text) ID.

I'd really suggest moving to Access as the storage repository for your data.
Excel's a great spreadsheet, but a spreadsheet is not a database!
-- 
             John W. Vinson [MVP]
0
John
2/27/2008 1:52:20 AM
Thanks.

Our mfg data is in foxpro, and the spreadsheet we have is the tool for 
costing, and my charter is to take someone else's spreadsheet, reverse 
engineer and upgrade.  My first task is as listed- to figure out costs for 
which historical does not exist. Best way forward is an Access db for 
recipes and then I can compare theoretical to actual (where actual exists) 
and figure batch size effect upon costs).

The DB is the best option for "recipes", and we will use an Acces  query 
into Excel (with refresh) to pull the data into the costing spreadsheet  via 
"vlookup".... (I assume Access has an equivalent for 'get external data'.

Thanks again!
Paul



"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:rfg9s3da6u0ntod9dt8cghv580v8qt2pvj@4ax.com...
> On Tue, 26 Feb 2008 15:28:15 -0800, "Anon" <anon@onandon.com> wrote:
>
>>Thats my problem- how to populate a field in Access table with a value
>>looked up in an external spreadsheet.
>
> File... Get External Data... Link to link to the spreadsheet; create a 
> query
> Joining the linked spreadsheet to your query by the six-digit (text) ID.
>
> I'd really suggest moving to Access as the storage repository for your 
> data.
> Excel's a great spreadsheet, but a spreadsheet is not a database!
> -- 
>             John W. Vinson [MVP] 


0
Anon
2/27/2008 3:58:59 PM
On Wed, 27 Feb 2008 07:58:59 -0800, "Anon" <anon@onandon.com> wrote:

>The DB is the best option for "recipes", and we will use an Acces  query 
>into Excel (with refresh) to pull the data into the costing spreadsheet  via 
>"vlookup".... (I assume Access has an equivalent for 'get external data'.

Access can communicate with Excel in a variety of ways; see the VBA help for
"TransferSpreadsheet" for one. You can also use VBA code to actually run Excel
and directly connect to spreadsheets, or you can use VBA macros in Excel to
hook into an Access table... that's outside my area of skill though!
-- 
             John W. Vinson [MVP]
0
John
2/27/2008 5:50:48 PM
Reply:

Similar Artilces:

sum value then insert row
How can I make for loop? the conditions are: if total value column height = 5 and column Article = "bike" then copy second row to row 6 (therefore 1 row added between file 5 and 6) thanks for your response ...

how to validate excel spreadsheets
Hi guys I am developing a document management solution in which some data will be received as an xl spreadsheet that complies to a certain pre-defined template/format (headings,datatypes etc) that maps to database columns where the data will eventually be saved.How can I go about this also taking into account that during validation all errors must be logged and routed to the respective stakeholders Please Help ...

Access Database Conversion to Excel Database
I need to convert a downloaded database in Access format to an Excel format I can use on my desktop. Hi Greg One way Use Data>Import External Data in the menubar -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "greg" <anonymous@discussions.microsoft.com> wrote in message news:b0f301c3ec2b$2b12d830$a001280a@phx.gbl... > I need to convert a downloaded database in Access format > to an Excel format I can use on my desktop. ...

Nothing shows up in Database objects in Database Wizard
Hello, I am having touble with Visio 2003 once again. Though the db wizard, I created an excel spreadsheet (table) for data and am tryinig to link the chart back to this original database so that when I change something in the database it will change correspondingly. I am going back through the DB wizard, and can only get as far as selecting a database object to connect to. I have selected the original dada source, checked table, but nothing showes up under the database objects. Am I or my computer missing something? Please help! Thank You. Have you defined a name for the region ...

Test cells for values if date matches
I have the following formula: =IF($AT$122:$BX$122=K$4,(IF(OR($AT$147:$BX$166="R"),"R",IF(OR($AT$147:$BX$166="Y"),"Y","G"))),"G") AT122 to BX122 contains a date (not all dates for a month are present) K4 contains the date I need to report on AT147 to BX166 contain the processing status by site for each date. I am trying to get the cell I am reporting in to display an "R" if any of the cells in the column that corresponds to the date that matches K4 contains an "R", if there is no "R", then I w...

Huge database
Good afternoon, i have to make a huge database wich have to get data from excel file.That is not a big problem problem is that i have to get information for about 1000 families with 1000 elements for every family and this database have to storage this information at leaset one year. This make a huge pack of elements. Can Microsoft Access get so huge pack of data ? If yes i would be glad to read some ideas. Thanks If you are talking about 1000 columns in a table, Access can't do it. 255 is the upper limit. 1,000 * 1,000 is potentially a million records. However if the data is pr...

linking 2 databases in 1
Hi, I am running 2 hqs for 2 different purposes, now the company decided to use 1 hq for both purpose. How can i link the 2 databases into 1 database???? I tired to link them from SQL server enterprise manager-all task- export data from database A to B but some tables can not be exported and show me tables failure, is it possible to link them or im just trying something impossible? Advice me on that plZzzzZZzz -- Aliko ...

#value
I have some functions calculating certain values. All is fine, but all of a sudden I have this strange #value sign there in the cells which contains the function to calculate some value. If I click on the cell, and then hit enter, it calculates fine. However, it doesnt calculate for the whole sheet by itself. I have used auto calculate and manual both, not working.(It used to calculate by itself before) Someone please help. Thanks, NM Try this: Private Sub Workbook_Open() Application.CalculateFullRebuild End Sub Save and restart the file... On 5 Oct 2006 11:28:29 -0700, "Nasir&q...

Importing Current Database into CRM Database
Hi, I would just like to know if it is possible to tranfer my current database into the Adventure Works Database(and if so how) or is it used just for a few records for testing purposes. You help will be highly appreciated... there is a redeployment tool on support.microsoft.com/downloads under microsoft crm. This may help you. -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Rock619" <rodger@intervigil.com> wrote in message news:5728edd40161d2cca1c0e079cfd86665@localhost.talkaboutsoftware.com... > Hi, > > I would just like to know if it is pos...

how to start a database
hi guys, i want to start a database on excel 2000, i never had to do this before, and i know this can be done. i just dont know where to start from ! what i want to do is, it is a racing club, i want a listing of all pilot and a sheet where we can enter the number of the car(all pilot info need will be transfer) for final result of the race.(right now they enter the car no, name , city at every race at every registration, if he is register to 3 race they do the process 3 time.) like in access went we create the database and after the query, that what i want to do in excel. any tip or link...

insert password protected spreadsheet
I want to insert an excel worksheet into another excel worksheet with the inserted worksheet password protected. When I attempt to insert a password protected worksheet, it asks for my password and then inserts the worksheet without the password. Every time you reply to excel with a password, you are either unprotecting or protecting. so once you unprotected it to insert it, you have to protect it again if you want it protected. "RZT27Y" wrote: > I want to insert an excel worksheet into another excel worksheet with the > inserted worksheet password protected. When I...

ByRef value not returned to the speadsheet
The argument, CompletionDate, is a reference to a cell (R7). I add 10 to CompletionDate. Debug.Print, in both cases, displays the correct value but my cell (R7) is not updated. My function resides in cell U7 and it's value is returned correctly. What am I doing wrong? Thanks for the the help. Function SetCompletionDate(CompletionDate As Date) As Date ' Correct value for CompletionDate is displayed via debug.print Debug.Print "1:" & CompletionDate ' Correct value for CompletionDate is displayed via debug.print ' but the value on t...

Use query from another database
Hi In the code below is there a way that qryOrderHistory could be in a separate database. If so how would i refer to it in the code. Thanks Dim db As DAO.Database Dim qd As DAO.Querydef Dim rs As DAO.Recordset Set db = CurrentDb Set qd = db.QueryDefs("qryOrderHistory") qd.Parameters("prmCustNo") = lngCustNo Set rs = qd.OpenRecordset() If Not rs.Eof Then .... code to populate an excel spreadsheet Change your 'Set db = CurrentDb' line to something like the following .. Set db = DbEngine.OpenDatabase("full path and name of target MDB here") -- ...

Database Queries with database views, that base on many database t
I created 2 database views. Each of them uses 256 database tables. From every database table only one column is used in the select list of the view. Every database table has one column which is used for the join. Each database table has 5 rows. The database tables have not any foreign keys and indexes. Both views works fine. When I use the database views in a query like this: SELECT v1.*, v2.* FROM v1, v2 WHERE v1.id=v2.id I should get 5 rows with 512 columns in the resultset. But I get the following error from the SQL-Server (after about 5 minutes): Msg 8621, Level 17, State 2...

Impact of changing database column width?
Hi, We are a software product company and are planning to begin using the Contract Administration module. To do so, we will be switching our inventory items from Track: None to Track: Serial Numbers and we will be storing our software license keys in the Serial Number field which is column SERLTNUM in table SOP10201. Unfortunately, SERLTNUM is CHAR(21) and our license keys are a few characters wider than 21. We can get the keys down to 21 characters by removing three embedded hyphens, but there are several databases outside of GP where the license keys must still exist with...

Excel Charts axis min, max values
How can I set the X and Y axis minimum and maximum values by reference to variable numbers in cells. -- Excel R&D Hi, You have to use VBA code to do this. Jon Peltier has an explanation http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html And Tushar Mehta has an addin http://tushar-mehta.com/excel/software/autochart/index.html Cheers Andy research wrote: > How can I set the X and Y axis minimum and maximum values by reference to > variable numbers in cells. > -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

suppress printing a repeating value in a column
In an Excel worksheet, how do you suppress printing a repeating value. For example, one hundred rows have the same date and I want the date value to print on a break only. Excel really isn't made to easily work with page breaks. Page breaks depend on the printer (which depends on what the user chooses). It can get pretty messy pretty fast. If you're willing to just show the first value and hide the duplicates (no matter where they occur, you could use conditional formatting--like Debra Dalgleish shows at: http://contextures.com/xlCondFormat03.html#Duplicate I've seen users ...

Set Database in Excel
In Microsoft article 183446 under method 2, it refers to using "Set Database" on the Data menu. I am using Excel 2002 (the article says it applies to Excel 2002), but Set Database does not appear in my Data menu. Is this an error? It's an error due, I think, to very old menu structures. The article says that the information applies to everything from version 5.0 (1995) through 2003, inclusive. But I believe that the Set Database menu item disappeared from the Data menu as of Excel 97. And one has long defined a name using the Insert menu, not the Formula menu. But my rapidl...

extract year from Date Value
Good morning, Could someone help me extract the year portion from a date value such as this 11/20/2009? Thanks in advance, Mike With the Date in A1, place =YEAR(A1) in B1 Takeadoe wrote: > Good morning, > > Could someone help me extract the year portion from a date value such > as this 11/20/2009? > > Thanks in advance, > > Mike ...

Client access problem after databases restoration
Hello! Recently we had a problem with an Exchange 2003 Back-End server with 6 mailbox store distributed thru 4 SG, and we lost the databases because the SAN's disks partitions were deleted by mistake. So, we had to do a restore from a backup, but when we could not mount the stores, so we used eseutil /p command, and after, eseutil /d, and then isinteg -s servername -fix -test alltests. We could mount the stores, users can use email. The problem now is that we have some users that could not access their mailbox thru Outlook, and when we try to move to another store, we receive an ...

Importing spreadsheet problems
I am trying to make my database user friendly as possible by making it so the user doesn't have to import the data from a spreadsheet through "file"- "Get External Data" etc... So I am creating a button that creates a table from a query, then goes into the macro and open the sheet and imports the information and then saves the information into the table. I am getting either one or the other error that depends on what I tell the macro "TransferSpreadsheet" to do. First is telling me that there is not "F1" field in the destination table. Although t...

upgraded from MSSQL 2000 to MSSQL 2005 now database's users have
Hello. I updated the MSSQL 2000 server to MSSQL 2005 server now the database's users have no login names. I found a way to list orphaned users sp_change_users_login 'Report' but it only listed dbo and there are actually five listed with MSSQL Server Management Studio Express. I tried this sp_change_users_login 'update_one', 'RPS', 'RPS' but I get the following error the user name 'RPS' is absent or invalid. Any ideas? Try with Auto_Fix instead; see the example at the end of the following article: http://msdn.microsoft.com/en-us/...

database or other software?
I need help with the following situation: I want to create files or a database that will contain information about my clients, with their business information (i.e address, phone, fax, type of business, in which states they do business) to send an invitation to bid on a construction project. I want , where a database would fill out the information automatically on the bid invitation, then fax, or email the invitation to each selected clients according to their specialty. Then I will need to keep track of who answers my invitations in order to "weed out" clients who do...

High and Low values in column
Is it possible to show the high and low values in column b based on the grouping in column a? I have a customer list with the prices each customer paid for the products. I want to know what the highest and lowest price each customer paid for their products. Is this possible using Excel 2000 or 2003? Thanks, Lee Coleman This is an array formula which must be entered/edited with ctrl+shift+enter =MIN(IF(C3:C33="customerA",D3:D33)) =max -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Lee Coleman" <lee@nospam.jjlgreenhouses.com> wrote in message news:...

Form in another database
How can I check if an Access form is open or loaded when the form is in another Access application (mdb or nde file). Alex ...