I am trying to come up with some type of spreadsheet that calculates my use of materials. I have an Excel spreadsheet that has the board feet of all my wood. What I would like is to “pull” that info, onto a new spreadsheet, when I use a certain amount of the wood for a box. Then add labor, overhead, etc. I think it would be fairly straight forward, but haven’t been able to figure it out. I do not have the resources to buy such an animal. I am a bit of a novice on Excel, so I’m looking for some guidance. Can anyone help?
Thank you.
Replies
It sounds like you need to "copy" the contents of a cell to the clipboard and then in the new worksheet "paste special" to make a "link". Are these worksheets each a separate file or are they all a part of the same book?
Having worked with Excel for a bunch of years, here is an easier way. Open the second worksheet. Click on the cell you want to fill. type the equals sign(=). then go to the first worksheet and click on the cell you want to copy. then hit enter. It really is that simple. This way, when ever you change a cell on the first worksheet, it will automatically change the value of the cell in the second worksheet that it is linked to. Hope this helps. Let me know if you have other questions.
FastEddie
I'm sorry, I thought you wanted it done the right way.
Thanks for you reply. I don't think I was clear. What I currently have is an inventory of board feet of each type of wood. What I want to do is have a new spreadsheet that takes a % of that board feet, depending on the size of my box. I never use an entire board foot. It would be easier to calculate that, but that's not what I use. I could end up using a board foot on 3 different boxes, in various stages. So I need something that will only take part of what I currently have. Does that make sense?
You are trying to do inventory right?
I assume you have a worksheet for each project and a worksheet that keeps track of in shop materials. Create another one to total up the BF of each project by type of material. Then link those totals back into your shop materials and use them to create a declining percent remaining in inventory.
Ok, I'm not being clear. I have a spreadsheet in Excel. On it is a list of all my woods, with the amount of board feet and cost per bd ft. What I want to do is create a worksheet for each project with material costs. What I mean is, I have 5 bd ft of bloodwood at $X per bd ft. I then, only use 1/3rd of a board to make a little box. I want a spreadsheet that takes from the first spreadsheet (my inventory) and calculates, based on my usage of the box I just made, how much (bd ft and $) did I just use to make this box. Then I want to add in a percentage for shop and hardware. Is that any clearer?
If you want your spreadsheet to keep a running tally of your inventory, it can be done but it is tricky. You're better off with a simple database application, instead. Using a spreadsheet to do a database job is like dimensioning wood with a router.
One bit of help: A key concept you should learn with excel is the use of named cells. When you select a cell, its address (like A1 for the top left cell) appears in a field near the top of the spreadsheet. You can name any cell by a (unique) name and then refer back to it. Using this type of system, I keep a worksheet of lumber prices with cell names like "Oak" and "hard maple"; when I have to calculate a cost, I can do it by entering a formula like =J8*OAK, where J8 would be the cell with the board feet of oak used for estimating a price.
good luck and keep at it. You can do a lot with excel, though it isn't the cure all to all problems.Recommending the use of "Hide Signatures" option under "My Preferences" since 2005
I think darbywoods uses what I use for my "regular work", we build cooling towers from wood and fiberglass.
We have a separate page in the spreadsheet with all the material costs in it in tables, which would be call the "Price" page. The tables in the page are "vertical lookup tables" with a name of the particular piece of material in the left most vertical column in the table. The cost of the piece or material is to the right of the named part. The vlookup comand you type into your work sheet will go to that table, on that seperate page, find the name of the part and bring back that cost for the part, to the cell that you typed the formula into. We also have tables for the particular man-hours to perform a particular task. The name of the part has to be DF-Douglas Fir or RW-redwood or QSWO-quarter sawn white oak. The formula would look something like =vlookup(QSWO, Cell range, 1). Right next to, to the right of QSWO is the cost, say 4.50 per board foot, or you could make it lineal feet. Whenever you need to update your costs you go to the master sheet and change it.
The cost estimate sheet you would use to calculate a particular job would have the formulas in it to pull from the master sheet the cost of the part, and the time it would take to make the part. You could add in whatever percentages you need for overhead, etc. on your cost estimate sheet.
The way I have done the sheets in the past is to sit down and draw it out on a separate work sheet. If you go to help in excel and type in vlookup or hlookup, it will tell you more of how this function works.
Hope this helps.
Steve Pickett
Just by chance, I'm dealing w/ a similar situation, but an entirely different application. I understand how to do relative and absolute cell references btwn worksheets in a currently open workbook, but is there a way to draw from or put information to a separate, 'master' workbook, w/o having to open it by hand and cut-n-paste from the one to the other? i.e. so if I change something in file 'A', worksheet '1', it'll update file 'B', worksheet '3'?Thanks,Monte
I can give you one tip that will help with your problem:
Put your "master" workbook in the XLSTART directory so that it will be open any time you start excel and link the data from the cells in the "satellite" workbooks to the "master" workbook.
For RobinLynn: what you're asking can be done, just complicated to describe in one of these posts. Perhaps others can better help. It certainly can be done, though - anything involving math can probably be done with Excel. I once built an application that did all of the performance data for flying a certain type of helicopter, based on entering just four environmental variables: Temperature, Pressure Altitude, Relative Humidity, and Helicopter Gross Weight. It even included popup dialogue boxes with slider controls to make it idiot-proof for my ham-fisted junior pilots. It spit out and printed things like: Safe Single-Engine speed, torque required to hover in ground effect/ out of ground effect, fuel flow, max engine torque, all kinds of stuff. To build this kind of application, you had to learn how to control excel with visual basic and learn some of the mathematical functions. For the right amount of money, I can be had to build a custom solution,...ha ha, just kidding,....As you work with it and learn this stuff, it's okay to curse Microsoft every step of the way,....many others have,...Good luck, Ed
Ed,I thank you for your help; I'll be sure to try that out!One other question, and I realize this is drifting a bit afield from the original scenario, and non-woodworking to boot...I've got some experience w/ using data from one cell or region in another, i.e. if I enter data on one worksheet, setting things up so that that data is used in another cell on a different worksheet in a calculation, etc.What I'm kind of hitting a wall on is this: I have a workbook set up to where I can enter the information on participants in an event... their contact information, member number, classification, scores, etc., and use Excel to format the information in a variety of styles, one worksheet formats it for printing for sending in to the organizing body, another for printing out for the competitors themselves (different subset of information), and another for calculating costs, award monies based on numbers of participants, entry fees, classifications, etc. (still fine tuning that last part). What I *want* to be able to do is have all the user information automatically copied to a separate worksheet... actually, copied wouldn't be the best term... appended would be closer to what I mean, for historical purposes. This starts getting close to being a database in effect, so w/o getting into VB programming, I'm not sure how to proceed?TIA,Monte
Monte, I think I can get you there without getting into Visual He77, I mean Visual Basic, but are macros okay? I think you want a macro that selects all the cells with new info, names it as a named range, then links it to a new area in the Master Spreadsheet. Then you put a button up on your toolbar that runs that macro, so every time you have a new person come along that you create new data for, you run that macro. You have to set it up so that the naming scheme is sequential "person99, person100, person101," etc. ) and the chunk of cells that it grabs and turns into a named range is sequential.,...You still will probably wind up editing a little VB in the macro,... but, that's the strategy I would employ. A book like, ..."Microsoft Excel 97 Visual Basic, Developer's Self-Study Kit" which comes with CD and probably could be had for less than $5 in a bargain bin or online somewhere will hook you up with the info. Good luck, sorry couldn't be more help. Ed
set your inventory on one sheet as you have it
use the vlookup function to type the name of the wood on a sheet.
enter the amount used of the wood.
have the lookup function pull the cost and do the calc's
this is the best way to solve your issue.
in addition you can use a funtion to enter hours used and then get a rate.
do not forget to add in an overhead cost and misc materials.
hope that helps
David
http://www.darbynwoods.com
Hello Robinlynn,
Since you have not filled out your personal info I don't know where you are at? I would suggest, you might go to one of the local Junior Colleges if there is one close and check with the Comp Sci dept. You might find those willing in the study labs to help you with this for free. I have found great help with many of programs that I choose to use without learning all of the nuisances to get done what I need to do for free from these sources. Also check with your local library.
Just my .02$
MK
This forum post is now archived. Commenting has been disabled