I need some relational database features for invoicing in MS Excel.
February 17, 2004 11:26 AM Subscribe
Invoicing with MS Excel--squeezing relational database features out [MI]
Response by poster: Assume that I sell a couple different types of customized widget. Widget A has a couple optional parameters (length, color), Widget B a few others (diameter, material, finish).
I want to automate the process of building an invoice. First I create a list of product names and prices. But I also want each product to be linked to another list of options, and each of those options needs to be linked to a list of parameters.
So Widget A links to a list called "widget A options" which should be a 2x2 list that looks like
Length | [link to list of length options]
Color | [link to list of color options]
And then I'd have two one-dimensional lists for color and length options.
Eventually all this would get linked into a new-orders worksheet...somehow.
So my questions are:
1. Am I completely insane for trying to do this in Excel?
2. If not, any tips on how to do it? So far, I haven't even been able to figure out how to get a list of option values to appear as a popup list for an option name.
posted by adamrice at 11:33 AM on February 17, 2004
I want to automate the process of building an invoice. First I create a list of product names and prices. But I also want each product to be linked to another list of options, and each of those options needs to be linked to a list of parameters.
So Widget A links to a list called "widget A options" which should be a 2x2 list that looks like
Length | [link to list of length options]
Color | [link to list of color options]
And then I'd have two one-dimensional lists for color and length options.
Eventually all this would get linked into a new-orders worksheet...somehow.
So my questions are:
1. Am I completely insane for trying to do this in Excel?
2. If not, any tips on how to do it? So far, I haven't even been able to figure out how to get a list of option values to appear as a popup list for an option name.
posted by adamrice at 11:33 AM on February 17, 2004
no matter what microsoft says, excel is not a relational database and lists of things stored in excel are no more databases than the piles of crap on my desk are databases. which is to say, they are databases in only the most broad definition possible. get the proper software for the job.
posted by quonsar at 12:09 PM on February 17, 2004
posted by quonsar at 12:09 PM on February 17, 2004
Response by poster: Yeah, I'd like to roll my own PHP/MySQL tool for this job, but my knowledge of both is pretty rudimentary, and even if it weren't, writing it would probably take me much more time than I could ever save back with it.
And, hell, I've paid for Excel, I might as well use it. Which is why I thought I'd explore this.
posted by adamrice at 12:12 PM on February 17, 2004
And, hell, I've paid for Excel, I might as well use it. Which is why I thought I'd explore this.
posted by adamrice at 12:12 PM on February 17, 2004
Does your version of Office include Access? This would be a cakewalk in Access.
posted by COBRA! at 12:16 PM on February 17, 2004
posted by COBRA! at 12:16 PM on February 17, 2004
lists of things stored in excel are no more databases than the piles of crap on my desk are databases
My understanding is that Excel files are effectively equivalent to single table Access databases. Which proves your point.
posted by yerfatma at 1:37 PM on February 17, 2004
My understanding is that Excel files are effectively equivalent to single table Access databases. Which proves your point.
posted by yerfatma at 1:37 PM on February 17, 2004
If you're the only user (or there are not many others) and you just want to churn out a reasonably small volume of invoices, I'm sure you could hammer together something in Excel that would work OK. I'd certainly rather do data entry in Excel than in a poorly done Access application. But, the minute you need to query it in interesting ways, manage mutiple users, or simply deal with a large volume of data, you will need an actual RDBMS application.
As to the question - I checked this on Excel 2002, and typing "combo box" into the help led to basic stuff on how to set the values for a drop-down box, etc. You could run an Excel macro when the first box changes that resets the rows of the second box, for example. Your problem is that Office for Mac is totally different, and I have no idea how (if at all) the VBA/Active X macro stuff is implemented in Office for OS X. If you just have cells and formulas, and don't have some kind of scripting tool, then it looks like time for plan B.
Perhaps there are Mac user groups for Office somewhere? The MSDN site also might have some stuff on their Mac products.
posted by crunchburger at 4:06 PM on February 17, 2004
As to the question - I checked this on Excel 2002, and typing "combo box" into the help led to basic stuff on how to set the values for a drop-down box, etc. You could run an Excel macro when the first box changes that resets the rows of the second box, for example. Your problem is that Office for Mac is totally different, and I have no idea how (if at all) the VBA/Active X macro stuff is implemented in Office for OS X. If you just have cells and formulas, and don't have some kind of scripting tool, then it looks like time for plan B.
Perhaps there are Mac user groups for Office somewhere? The MSDN site also might have some stuff on their Mac products.
posted by crunchburger at 4:06 PM on February 17, 2004
I'd just shell out for FileMaker. And hey, it looks like they have a $50 rebate for Excel users.
posted by staggernation at 5:28 PM on February 17, 2004
posted by staggernation at 5:28 PM on February 17, 2004
Hmm, actually it looks like that's a $50 rebate for anyone.
And here's the FileMaker for Excel users page.
posted by staggernation at 5:36 PM on February 17, 2004
And here's the FileMaker for Excel users page.
posted by staggernation at 5:36 PM on February 17, 2004
« Older Does anyone have a relatively safe & painless... | How can I keep my paintball mask from fogging? Newer »
This thread is closed to new comments.
posted by brownpau at 11:32 AM on February 17, 2004