Wizards of Excel, lend me your wand.

frobisher

I'm reasonably adept with Excel. But I just can't seem to get a handle on how to start my latest project on it. I want to be able to make a packing-list by punching in basic specs onto my spreadsheet eg. If I need to pack wood for building a panel wall I can type in the panel specs 4 wide x 4 high and it will tell me 16 panels need to be packed, but also other relevant info for my packing-list like how many joiners (2 between each panel), how many support beams etc.

I hope I'm making it clear! Any help on how to make this would be great.

Jimbo

Well if there is a joiner between each panel, the numer of joiners would be: (no of panels -1). If the number of panels calculated is in, say, cell B3, the formula would be =(B3-1)

KAGY

frobisher wrote: »

I'm reasonably adept with Excel. But I just can't seem to get a handle on how to start my latest project on it. I want to be able to make a packing-list by punching in basic specs onto my spreadsheet eg. If I need to pack wood for building a panel wall I can type in the panel specs 4 wide x 4 high and it will tell me 16 panels need to be packed, but also other relevant info for my packing-list like how many joiners (2 between each panel), how many support beams etc.

I hope I'm making it clear! Any help on how to make this would be great.

Without knowing more here are some hints,
make sure you type in the heights and widths in separate cells
If you want to make your formulas easier to read have a look at naming cells, the easiest way is to click on the cell, then type a name in the formula bar where it shows the address e.g. change B2 to Height, B3 to Width, etc.

If you are just working with the same things each time then down the rows list each item that needs to be packed, I'm thinking 4 columns for each (description, number per height, number per width, total). Number per height = 2 * (Height -1) or what ever.

If you want to cover many different situations you will have to look into some of the more advanced / database functions of Excel. The most important ones are Match() and Index(). (I tend to use these more than lookup() )

On a separate sheet type something like the Panel array here: select it an give it a name, e.g. PanelArray

Panel Name    NumJoiners    NumWidgets   QtyGlue
Panel type 1    2                     5            100
Panel type 2    4                     3             0

Use a drop down box, showing the first column of panel array below and linked to a cell say B5. If you choose the second item in the list, then B5 will have 2 in it.
To choose something, lets say the Number of Joiners needed per panel, fromthe array use:
=index(PanelArray, B5, 2) [you might need to switch the B5 and 2, one is the row, the other is the column, I keep forgetting which]



If something depends on a range use match()
=match("list to match against", "number to match", "type e.g. 0 = exactly equal")

frobisher

KAGY wrote: »

Without knowing more here are some hints,
make sure you type in the heights and widths in separate cells
If you want to make your formulas easier to read have a look at naming cells, the easiest way is to click on the cell, then type a name in the formula bar where it shows the address e.g. change B2 to Height, B3 to Width, etc.

If you are just working with the same things each time then down the rows list each item that needs to be packed, I'm thinking 4 columns for each (description, number per height, number per width, total). Number per height = 2 * (Height -1) or what ever.

If you want to cover many different situations you will have to look into some of the more advanced / database functions of Excel. The most important ones are Match() and Index(). (I tend to use these more than lookup() )

On a separate sheet type something like the Panel array here: select it an give it a name, e.g. PanelArray

Panel Name    NumJoiners    NumWidgets   QtyGlue
Panel type 1    2                     5            100
Panel type 2    4                     3             0

Use a drop down box, showing the first column of panel array below and linked to a cell say B5. If you choose the second item in the list, then B5 will have 2 in it.
To choose something, lets say the Number of Joiners needed per panel, fromthe array use:
=index(PanelArray, B5, 2) [you might need to switch the B5 and 2, one is the row, the other is the column, I keep forgetting which]



If something depends on a range use match()
=match("list to match against", "number to match", "type e.g. 0 = exactly equal")

Would you fancy doing a nixer?!

KAGY

frobisher wrote: »

Would you fancy doing a nixer?!

If you asked before Xmas I would have said yes, but I'm up to my proverbials now for the next month or two!
I won't mind helping a bit with a particular problem, and sure, if it's still up in the air after Easter, send me a message and we'll see what we can do!

The big thing is get it down on paper with a work flow, there should be no cases where you can say "well the formula says 3 joiners but at this size we usually use 2". If it doesn't formula-ise well, then get it down in a table or list.
Then try using the paper trail to see if you can work out everything that you need before trying to program it.

Hope that helps,