Excel and databases?

emt8q5

macrumors member
Original poster
Jul 21, 2007
79
0
Hey folks,

I'm a basic excel user here, nothing too advanced. I use Excel for MAC 2011. I'm trying to do a project for work which requires some advanced (to me) functionality. I think I'm pretty good at self teaching, I just need to know where to start looking to learn how to do this type of stuff. I'm hoping you guys can point me in the right direction.

Here's what I want to do:

I work for a specialty construction contractor as an estimator/project manager. I currently use excel to put my estimates together, but it only has some basic formulas for calculation.

I'm hoping to create a workbook that will allow me to efficiently put together pricing for installation of traffic signage. Every quarter, we get a catalog from our supplier with costs for all the components that go into a sign (sign panel, post, concrete footing, fasteners, etc...). I want to create a spreadsheet that allows me to use drop down menus to build a sign out. The drop down menus would reference the current pricing info from a database I create

I know how to do the basic formulas (i.e. take material cost and generate profit margins). What I don't know how to do is create a database of items and costs and then have excel reference this database

For example, I would like my worksheet to look like this:

---------------------------------------------------------------------

SIGN TYPE: <user types in sign name> example: ""30'x30' Stop Sign into landscape"

QUANTITY: <user selects quantity numer> example: "3"

MATERIALS:
POST TYPE: <drop down menu of post types> example: "Green Channel Post - 8' "
PANEL 1 <drop down menu of sign panel types> example: "30"x30" STOP

PANEL 2 <drop down menu of sign panel types> example: "6" x 18" PEDESTRIAN CROSSING"

ADD 1 <drop down menu of general add-ons> example: "Concrete Footing"

ADD 2<drop down menu of general add-ons> example: "Fastening brackets"



LABOR PER SIGN - MAN HOURS <user types numerical value>

COST: <value collected from adding together above selections>
MARGIN <takes costs and calculates profit margins at various percentages>
---------------------------------------------------------------------

What do you guys think? Is this beyond what excel is capable of doing? Where should I start looking to learn about this? Can I create a table for pricing, etc inside of my Excel workbook or should I look elsewhere?

Thanks!
 

MisterMe

macrumors G4
Jul 17, 2002
10,648
29
USA
You are not here looking for free consultants to help you run your business, are you? There are people who make their livings doing what you are asking us to do without compensation.

Having said that, Excel is a spreadsheet application, not a database managment system (DBMS). It sounds like you need a custom application built using a DBMS. The most popular DBMS is the excellent FileMaker Pro from Apple subsidiary FileMaker, Inc.

My advice to you is to first investigate the vertical market applications that are targeted at your industry. If none do what you want, then you may need a custom application. Understand this, however. If you need an application that is not available on the open market, then you will need to work with an application developer who knows and understands your firm. It is impossible to convey that level of knowledge using descriptions of your wants on this or any other computer fan site.
 

emt8q5

macrumors member
Original poster
Jul 21, 2007
79
0
miles01110: Thanks for the advice. You're right, Macros are part of it along with VLookup and a few other functions. Didn't need a "database," just a table of prices on another page in the same workbook. Pretty straightforward, I've got a working model set up, polishing it now.

MisterMe: I posted this on another forum as well and got a few responses. Based on those responses I was able to research the necessary functions and put together a working model of what I wanted in less than an hour. Really not that complex. No need to "Investigate the vertical market applications that are targeted at your industry." I just needed a spreadsheet.
 
Last edited by a moderator:
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.