Help eliminating need for merged cells in excel

Feel like I can't see the forest for the trees. Here's the scenario ... in an excel 2003 file I have 30 sheets, each sheet with information about 25 "boxes". Within each box, there are 1-10 "pieces". The "pieces" need to be assigned to each "box". Each "box" also has up to 5 attributes, and each "piece" has up to 5 attributes.

Currently, I am listing the "pieces" vertically (say C1 thru C10), and the "piece" attributes are then D1 thru H10. One of the attributes is "piece" quantity - which will vry by "piece" and "box". Unfortunately, because I am assigning the pieces to a specific "box", I am merging A1 thru A10 (assigning it the name "box1") and merging B1 thru B10 (assigning it a quantity value).

Ultimately I need to obtain a total quantity of pieces, and total quantity of boxes. I would like to multiply the "piece" quantity by the "box" quantity. However, since the "box" quantity is a merged cell, the formula doesn't work. I know that I can take the upper left cell and use it for each formula - but that's a lot of manual entry because the merged cell range can be different for each of the 25 "boxes" on 30 sheets. I would like one formula that could recognize the upper left cell (whether开发者_运维百科 there are 2 cells in the merge or 10).

So ... my questions are: 1. is there a formula? (preferably not udf or macro since I am on the low end of the learning curve and those I work with are lower) 2. do you have a suggestion on how to set up a format that allows me to eliminate the merged cells and prevents each box from having 55 columns of information?

Thank you and apologies for my forest blindness.

---

add an intermediate result column (out of the way from your data columns) with this formula: assuming merged cells are in column B, add formula to column Z as an example:

=IF(B1="",OFFSET(Z1,-1,0),B1)

this will place the value of the first cell in the merged range, into each row the formula is in. use this column instead of the merged cell column in your formulas. add a similar formula in another column for each column containing merged cells, (probably A thru H)