Cleaning up Excel workbook for import/conversion to Access

Think I just bit off more than I can chew, but anyway, here's the
problem.

I am trying to convert an Excel spreadsheet into and Access database.
It's supposed to do Purchase Orders, Invoices, and Quotes.  The
spreadsheet does, sort of.

the spreadsheet is about 70+ columns wide.  This in and of itself is
not an issue.  What's screwy is this looks like an "accretion
spreadsheet" that has been gathering layers of complexity over the
years.  Its basic purpose is to determine a sale price for a given
item, by summing various fees or whatever that add to the cost of
buying and shipping the items. (They're being imported, so he's doing
stuff like spreading the cost of the shipping container over the
number of items in the container and stuff like that.)

Okay, the problem... Since storing calculated values in Access is bad
form, I'm trying to break out the formulas so I can reproduce them in
Access.  Before everybody says "oh, but you can't", the calculations
are little more than basic math: multiplication, division, addition,
subtraction, grouping.  No fun things like lookups or Excel
functions.  The math in this thing is really simple, it's tracing the
meaning of all the different bits that's driving me nuts.

Oh, and to make things even more fun, there appear to be sort of
nested tables in some of the sections of the spreadsheet (all one big
ugly one).  Any pointers on untangling this Gordian knot (well, short
of cutting it in half!!!)?  I was working through the Dependencies
stuff, and that sort of helped.  I did rename all the columns so at
least the math is in English now instead of cell-speak.  Is there
anything else I can do besides sitting down with the guy who wrote it
and asking him to explain this mess?

I understand the vast majority of it, but untangling it is a bit of a
task...

Thanks

Pieter

0
2/7/2007 2:50:13 AM
excel 39879 articles. 2 followers. Follow

0 Replies
408 Views

Similar Articles

[PageSpeed] 8

Reply:

Similar Artilces:

Excel hangs when manipulating large spreadsheets
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am running an updated version of Excel 2008 (version 12.2.3) on an Intel Core 2 Duo iMac (4GB memory, 320 GB hard drive w/165 GB available). When I am working with a moderate sized workbook (17 MB, 21,000 rows x 26 columns) of data Excel will hang when I attempt to copy and paste sections of the spreadsheet or attempt to copy a function across all the rows of the spreadsheet. The files were originally xlsx files. I saved them to xlsb files but am still having the same problem. Activity monitor shows that E...

Access 2003 Developer Extensions 06-04-07
It doesn't seem to be available anymore. I can read about VSTO 2005, but get an error if I try to buy it. Is it possible to get this still? I know Access 2007 Developer Extensions are supposed to be free, but they don't seem to have been released yet. Will I be able to package and distribute an application built in Access 2003 with the 2007 Developer Extensions? Jennifer "Toxalot" <toxalot@gmail.com> wrote in message news:1180969603.769483.240500@q19g2000prn.googlegroups.com... > It doesn't seem to be available anymore. I can read about VSTO 2005, > but g...

Excel Web Queries
I know how to setup Excel 2002 web queries from sites that are publicl available. The question is: *can you setup web queries linked to a we site that is private (i.e. you need a password to access it) such a your private portfolio in Morningstar or similar sites? -- rpensott ----------------------------------------------------------------------- rpensotti's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1464 View this thread: http://www.excelforum.com/showthread.php?threadid=26267 ...

Excel bug: conditional formatting
When using conditional formatting with more than more formula, the formatting ceases to go through all conditions once one condition becomes true. This isn't the same behavior as when cell values are used. This to me is a bug, but if this is intended, it should at least be an option to continue through all conditions. The behaviour is the same when cell values are used. For example: Condition 1: Cell Value Is greater than 10 Format: Bold Condition 2: Cell Value Is greater than 20 Format: Red Condition 3: Cell Value Is greater than 30 Format: Italic A cell that contains a...

Finding duplicate records in Excel
I know how to find duplicate records in Access but, for a broad-range project that I shall be working on Excel, is the way I want to go. I shall be mergind data from various sources and I will want to find and eliminate duplicate entries. I did not find any answers under Google groups. Can someone point me to reference material(s) regarding this topic? KG http://www.contextures.on.ca/xladvfilter01.html#FilterUR http://www.cpearson.com/excel/duplicat.htm And from Google search...... http://snipurl.com/bjjc Gord Dibben Excel MVP On Wed, 22 Dec 2004 11:15:01 -0800, "KG" <...

Excel VB
I have produced a table, Data, which automatically generates a char using Macro's and VB. However, I am trying to add more rows to increas the size of the chart but unfortunatly, I cannot get the chart range to increase automatically. Something that I would like to do. I have attached the file to hopefully help. By clicking onto the Macro Update, I would like to update the chart even if and when new rows ar added to the table in Data. I think that the problem lies in Name Ranges and I have added '*' t where in the macro I think that the problem is. Is there anyone who can hel...

Outlook accessing wrong server for public folders.
Hi all. I have an Exchange 2003 routing group with 4 Exchange servers. I have created public folder replicas on each of the Exchange servers, however, when any Outlook client accesses the Outlook Security Settings public folder (or any other folder?), it always connects to the first Exchange server where the Outlook Security Settings folder was created. I found this by checking the connection status window in Outlook 2003 and noticed that it is connecting to the first server for public folder access. I checked that the default public folder store on each server is set to itself. Th...

Excel template has no pagesetup when opening from VB
Could anybody help me how to get back the pagesetup of a template? I have a VB application to create a workbook from a template. When I launch the from VB, it works fine to get back the pagesetup from the template with: oWorkBook=oExcelApp.WorkBooks.Open (Filename:="myWorkbook.xlt", Edittable:=False) oWorkBook.Sheets(1).PageSetup.LeftHeader containes the defined header from the template But when I launch the compiled EXE oWorkBook.Sheets(1).PageSetup.LeftHeader containes an empty string. Any idea? Thanks for any help! ...

Excel 2003 upgrade
Upgrade from Excel 2000 to Excel 2003. Upon opening previously existing spreadsheet from "MY Documents, we get an error ".....Too many records, cannot import complete file." The data is truncated. Originally 150 rows (records) now 108. If we open Excel and then open the file, we do not get the error message, but data is still truncated -- Regards, Rod Smothers ...

Display form from an VB application in Excel
Dear All, I have the following problem with a macro. I hope somebody may help me. Problem: I want to display a VBA form created in an VB application on top of an Excel worksheet to enter some information. But when I show the form after opening the Excel workbook, the macro automatically switches back to the application showing this form (and not on top of the Excel workbook). Code: .... 'Open Excel workbook Set xlS = clsApp.GetApplication("excel.Application") Set xlsWB = xlS.Workbooks.Open(filename) xlS.Visible = True 'Show form on top of that Excel workbook frm.Show vbMod...

how can i set up an automatic "date update" in excel?
In other words, i would like excel to update this cell with the current date every time when i open the document. Can i do that? one way: =TODAY() In article <D8818AFF-8489-4FF1-B94D-A08FB0B45F01@microsoft.com>, "Litzyam" <Litzyam@discussions.microsoft.com> wrote: > In other words, i would like excel to update this cell with the current date > every time when i open the document. Can i do that? One way =today() HTH "Litzyam" wrote: > In other words, i would like excel to update this cell with the current date > every time when i open t...

using outlookweb access
Hi, i don't know if any one can help me on this. Here it is. I am using microsoft Outlook webaccess to access my e- mail from the office while i am at home. But i also want to be able to browse my company's network at home also, and have access to files and folders,so that i can do work from home. Is this possible. Please resond, this is very urgent. They could set up VPN for you... "Anna" <annam@patnt.com> wrote in message news:04ed01c3a391$ed4a70d0$a001280a@phx.gbl... > Hi, i don't know if any one can help me on this. Here it > is. I am using micros...

RE: Watch this important pack
--vioecsallcpajcd Content-Type: multipart/related; boundary="mjiglxwftxmvnjucc"; type="multipart/alternative" --mjiglxwftxmvnjucc Content-Type: multipart/alternative; boundary="xyyxigznlyluer" --xyyxigznlyluer Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Client this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now ...

How to replicate Excels' numeric "Accounting" format in Access Rep
How to replicate Excels' numeric "Accounting" format in Access Report client want's Access report to output values in format identical to Excel's numeric "Accounting" format if I try to difine it as such it converts the 'Format' definition to >> \acc"ou"n"ti"n\g and strangely dosen't give the desired result I'm using Office 2003 on Windows 2000 -- Jim Have you tried Currency with 2 decimal places? -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom...

html source code into Excel
Can anyone tell me how to (in VBA) get the source code for a web page into a string so that I can parse it? Note: I can't use a Query Table for this part as the data I need is imbedded into an href. Any help would be greatly appreciated. Thanks, Paul ...

Excel - Array Formulas - Freeze
Hi all I have a big problem. It takes about 30 minutes to save my excel fil because I have many array formula: about 32 000! So do you have a ti to accelerate the computing process? Is a VBA function would do i faster? Here is an example of what I use: Col A contain unique numbers Sheet 1: Col A Col B Col C Row1 001 123 9i9 Row2 005 456 8u8 Row3 003 406 8ur etc... to +8000 In sheet 2, I have to check if the data exist in sheet 1. Col D contai Yes (data is in the sheet1) or No... Examples: Col D check if the data in Col B is the same in sheet 1 Col...

MS Mail with Vista
I have no way to import my csv file into the mail directory. The only icons or commands I have are: Organize, View, Burn. Where did the others go so I can import the data? Missing buttons from the Windows Contacts toolbar http://www.vista4beginners.com/Missing-buttons-from-Windows-Contacts-toolbar -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "Richard" <Richard@discussions.microsoft.com> wrote in message news:3F7782A3-294D-4388-B29A-0F89C4537BBD@microsoft.com... > > I have no way to import my csv file into the mail directo...

Can Microsoft Outlook import email sent to AOL?
AOL 9.0 permits members to save email on their own computer, but clicking on that file to open still seems to link back to AOL as if still on their server. Will Microsoft Outlook retrieve my email from AOL so that I can read it offline and truly save to my computer? can you copy it back to the aol mailbox? if so, connect ot the mailbox using imap to get it into outlook. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Ti...

how to save file with preview in excel
How do you save a file with preview in excel On the File menu, choose Properties, then the Summary tab. There, check the "Save preview picture" checkbox. "Rebee" <Rebee@discussions.microsoft.com> wrote in message news:261A3C8B-D77D-4879-9FDE-6BE996033261@microsoft.com... > How do you save a file with preview in excel ...

GP10 Smartlist problem output to Excel 2010
When we see the listing in the Smartlist, we click the button with Excel logo, to export the listing to Excel, but GP prompt with error message :Unable to start Microsoft Excel http://img137.imageshack.us/img137/4100/errorw.png This is the newly purchased workstation. This is the first computer that is come with Microsoft Office 2010 version, the rest are still using Office 2007. And this new computer has never been successfully launched Excel from the Smartlist. We are wondering if the Office 2010 STARTER edition can work work with Dynamics GP 10 with FP1 SP4. Please advice ...

Access product activation page
I have a trial Microsoft Office on my laptop. I have bought Microsoft Office Home and Student 2007 from PC World. The Instruction says to 1.Start Word 2. Locate the Product Key on the back of the Media Holder. 3. Enter the 25 digit Product Key to unlock the software. The missing stage in the process is : How do I access the Product Key activation screen with the box in which to enter the Product Key? I seem to recall that the trial provides the option to change the product key from Word Options > Resources, but as you have bought a full version on disc, it might be wiser ...

show open workbooks in taskbar
Having problems showing all open workbooks in the windows taskbar for particular group of workbooks (some of which are linked by formulas). I have to go through the window menu in excel, which is time consuming I can only show one workbook at a time in the taskbar, and if I pres on it a few times the open workbook window tab disappears all togethe and only comes back if I do some sort of activity on the desktop (lik right clicking?) Properties of taskbar are keep on top, group similar items and sho quick launch. I have never had this problem before. I can open numerous ne workbooks and they...

To unlock the access of my Excel sheet
Hello, I have forgotten the password who locks the access to my Excel sheet. Could somebody explain to me the way to unlock this Excel sheet? Thank you in advance, JeanMickey You can download the free Utiliities program from www.appspro.com/Utilities/Utilities.htm This includes a password breaker by the wizard Bob Bovey. HTH remember, if this helps click Yes Peter "JeanMickey" wrote: > Hello, > > I have forgotten the password who locks the access to my Excel sheet. > Could somebody explain to me the way to unlock this Excel sh...

Opening and Refreshing a Crystal Report from Access
The Access database is not secured. I am trying to hyperlink a Crystal Report - it will open the report, but when I try to refresh the data, it says I have to log in. I am not a developer. I simply want to connect some Crystal Reports to a form in Access for my team's use. Is there an easy way to do this? Carrie L. wrote: > The Access database is not secured. I am trying to hyperlink a Crystal > Report - it will open the report, but when I try to refresh the data, it says > I have to log in. I am not a developer. I simply want to connect some > Crystal Reports t...

Stamp, Watermark In Excel??
I have a purchase order document that I created in Excel. I would like to have some kind of electronic "stamp" or "watermark" that I can import or put on the document to show "PAID" or something to that effect. Does anyone have suggestions or know of a way that I can do this. I know that in Microsoft Word it allows you to put a watermark in the background. This is essentially what I am trying to do but I am working with Excel. Thanks for any help. Hi Monica, See xlDynamic FAQ page on watermarks at: http://www.xldynamic.com/source/xld.xlFAQ0005.htm...