Excel and databases?

Discussion in 'Mac Apps and Mac App Store' started by emt8q5, Jan 18, 2012.

  1. emt8q5 macrumors member

    Joined:
    Jul 21, 2007
    #1
    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!
     
  2. MisterMe macrumors G4

    MisterMe

    Joined:
    Jul 17, 2002
    Location:
    USA
    #2
    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.
     
  3. miles01110 macrumors Core

    miles01110

    Joined:
    Jul 24, 2006
    Location:
    The Ivory Tower (I'm not coming down)
    #3
    A macro-enabled Excel sheet should be able to do what you want.
     
  4. emt8q5, Jan 19, 2012
    Last edited by a moderator: Jan 19, 2012

    emt8q5 thread starter macrumors member

    Joined:
    Jul 21, 2007
    #4
    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.
     

Share This Page