How do I link excel spreadsheets to create an inventory breakdown?

July 14, 2009 12:16 PM

How do I create an inventory database using Excel?

My mother is an artist, and computer challenged. (Often, her broken CD/DVD player requires turning the disc right-side-up to "repair".)
(I say this, not to be cruel or mean-spirited, but to emphasize that I'm trying to set something up that I can lock so she can't create more troubles for herself by accidentally highlighting the wrong cell in a spreadsheet)


Her work, a steel-and-magnetic-tile composite is priced based on size (area) and component makeup. (i.e. 10$/inch^2, plus 20% when made of copper, 25% stainless steel, etc)

Each "Piece" of artwork is composed of between 4-10 "Tiles" which are a breakdown of the individual componants. (Tile 1 is usually a large piece of plate steel used as backing, then tiles 2-X are the actual magnetized tiles that affix to it.)

MS Excel is our friend here; I created a spreadsheet invoice template that lets her insert a picture of her piece, then below it, list the components, so as to give the customer a breakdown of the price. Then, I locked all the formulas so that she didn't have to come up with a price herself-- it's calculated as the end-result of the price/area*area*materials-markup equation.


An example:

::picture::

Description ------- Width ----- Height ----- Area ----- Materials Markup---- Total Price
1.
2.
3.
4.
5.
6.

:: now, at the end, a formal-looking grand total, section for notes, etc::



Now, my question: She needs to be able to create a complete inventory database, but at the same time, keep it pretty simple.
This is what I'm trying to set up:

MASTER.xls (a master sheet)
Piece 1.xls
Piece 2.xls,
etc.

The Master sheet is where I have my question.
I want to be able to link each of the "Piece X.xls" sheets to the Master, so that the master is basically a compilation of all the information from each of the Piece sheets, plus the a note on which piece it came from.

This way, the master sheet'll look basically like this:
Description ------- Width ----- Height ----- Area ----- Materials Markup---- Total Price ---- Piece Location
1.
2.
3.
...
(The list'll keep growing every time she adds a new Piece sheet to the folder)

The Master sheet will be a long list of every tile in every piece, so that she can keep track of them.
How do I link this information?
When I link it, I want to make sure that when she adds a tile to any of the individual "Piece" sheets, it updates the "Master" sheet.
(for example, "Piece 1" was originally 6 tiles... she created a seventh, and added it to the composite Piece. As it stands now, all she'd have to do is take a new picture, and enter the description, width, height, and select a markup from a dropdown box I put in there--- I want to make sure that this would be reflected on the Master sheet)


The "Piece X.xls" sheets will be the same format I've posted up above. They're printable, editable invoices that she can print out and mail with the piece to a client.

If at all possible, I'd like to be able to, in the master, include a link to a folder where I have a picture of individual tiles-- so she can link to a picture. (But that's the next hurdle)


Thank you for your assistance!

posted by Seeba to computers & internet (10 comments total)

If it helps, here is a link that has examples of the work if it'd help to visualize what I meant by compilation of tiles.
posted by Seeba at 12:18 PM on July 14

Do you have access? It would be a milion times better for this.
posted by jenkinsEar at 12:22 PM on July 14

jenkinsEar has it. You are describing a database, not a spreadsheet. Access will let you create the underlying tables, easy to use forms that will let her enter the necessary details, and pretty reports that can act as invoices.
posted by i love cheese at 12:37 PM on July 14

Thank you-- I'd never checked out access before-- it looks like it'll do most of what I want; how do I set up the pricing equation?
(All I want her to do is enter the length/width of each tile, then have the computer do the math on the price X$/inch^2, * (dropbox-with possible markups).

Almost better would be her entering Length/Width, then the computer giving her the area, then multiplying it by another drop-box which would have variable prices/inch^2.
posted by Seeba at 12:58 PM on July 14

Try this guide on Calculated Fields.
posted by odinsdream at 1:02 PM on July 14

A Pivot Table can consolidate across multiple worksheets. Here's a useful guide.
posted by IanMorr at 1:35 PM on July 14

Ha! This question always raises the ire of database geeks. And they're right, Excel is not a database.
posted by exhilaration at 3:57 PM on July 14

Database geek here.

DO NOT DO THIS. EXCEL IS NOT A DATABASE. YOU WILL COME TO REGRET IT, AFTER SINKING LOTS OF TIME ON "ONE LAST FIX".

Use Access (which is not really a real database, but is sufficient unto your single-user needs).
posted by orthogonality at 7:53 PM on July 14

Another database geek...

As orthogonality said, this is a bad idea. Excel is a fantastic tool, but it's not a good one for managing this type of data.

I'd recommend looking at FileMaker Pro, which is a good database tool which is both easier than Access and more powerful—in ways that you'd notice even in a simple application like this.
posted by jerwood at 9:48 PM on July 14

Awesome, thanks y'all.
I looked into Access, and then tried FileMaker-- a great program that's been really easy to set up and lock down.

Thank you!
posted by Seeba at 2:38 PM on July 15 [1 favorite]