Can this be done: Calculate only selected cells

[roseuz]

I have a file with a list of items. For each item, there are dimensions, weights, and other info to go along with it. Each of these specifications are on a column of their own and each item and its corresponding info are on a row. You will see this in the attached file.

I need to calculate the cubic inches for each item, and also a grand total. I also need a grand total of the weights of the items for items that are selected.

So, for example:

Item 8989: 12x12x12 10 lbs
Item 9393: 3x3x3 8 lbs
Item 3434: 4x4x2 12 lbs

So, if I select the last two items, I need to have the following information:

Total Number of Items Selected: 2
Total Cubic Inches: 59 inches : (27+32)
Total Weight: 20 lbs (8+12)

Selection Explanation:
I have arranged this information in the attached Excel file in what I feel is the best way for me to instantly see the information.
The x in the first column of the file means that I selected that item and what it to be included in the total calculation. If this can be done with a checkbox that might be preferred, but I think an x or pressing a space bar to indicate a selection (whatever is easy to select on the keyboard) is also o.k.

Dimensions Column: Variations
Now, I think the dimensions will all be standardized, meaning that they are all written in the same way: i.e. all written as 11"x12"x15", but I don't remember right now. It would be preferred if the dimensions are written in any format, that that is accounted for. So, the dimensions can be written as 11x12x15 (so in this case without the quotation marks) or 11 x 12 x 15 (in this case no quotations and with spaces), etc. I have specified different variations in the attached Excel file. As you can see from these examples, stripping all characters except numbers and leaving one space between them seems like the solution.

Weight Column: Variations:
Also, for the pounds, I can't remember right now (I don't have access to the file now), if the weights field has lbs in it along with the number or if just the number. Again, it would be great if the program can accommodate either option.

So, is this something possible in Excel. I think the answer might be yes, but on the other hand I think it's only possible via the use of a macro. And if yes, will the macro work in Excel 2003?

[Beamernsw]

Hi roseuz, I've attached your file again with some formulae. When you find out which format the size is in you can just use the corresponding formula from your sample.

[roseuz]

Thank you very much. Works great and doesn't involve macros, and though the data I will be using is all standardized, I am a bit worried that maybe there might be one or two entries that are not standard like the rest (for example, an extra space is added, or the " (quotation mark) is missing)), and causes a problem. I guess I could just hope those instances are rare and adjust the formula or the carton size entry accordingly.

I have a question. Is there an easy way to select the entire of column A regardless, regardless of what cell I am on in that column to be able to delete the x's? Ctrl + Space works nicely to select the entire column, but it also selects the first three cells, which must never be deleted, and I can't figure out how to unselect those cells anyhow. I suppose, one easy quick solution would be to move the Total count of the items over to column B. I am still interested however in other approaches and would like to "hear" what you or anyone else has to say.

[roseuz]

Double Post

[Beamernsw]

I came up with another solution that works for ALL your examples with the same formula. Which means if any are formatted differently it won't matter. But it does require a very small macro. Just need to add the Evaluate function into a module.

[Beamernsw]

Forgot to add the file and it won't let me edit the post or attach the file here. I'll come back later and repost.

Actually, I'll give instructions here:

Right-click Sheet1, click on View Code
Right-click Sheet1 in the project tree, select Insert, then Module
Paste this in:-

Please Login or Register  to view this content.

Now you can close all that.

Copy this into cell H4 and copy down:-

Please Login or Register  to view this content.

Done

[roseuz]

Thank you so very much. It works great. Simply saving it under the .xls extension is all that's needed to get it to work in a 2003 version of Excel, right?

Also, while I don't think there is a solution, do you want to maybe take a look at my other problem and give me your thoughts: http://www.excelforum.com/excel-gene...e-updated.html