Excel - Carton size and cubic feet auto calculate..

Asked By shawn on 23-May-07 01:50 PM
I have an Excel sheet for our "Shipping Manifest". It shows how many pieces
in a carton, carton size, cubic feet, etc.

We have standard carton sizes we use which we've named.

Carton A = 18 x 18 x 18 = 3.38 Cubic Feet
Carton B = 18 x 18 x 14 = 2.63 Cubic Feet
Carton C = 18 x 16 x 14 = 2.33 Cubic Feet
.. etc, on down to Carton J.

Is there a way to make it so we can type A, B, C, etc under "CTN SIZE"
column it will automatically enter the cubic feet for us under the cubic
feet column? Then at the bottom of the sheet we'll have a total which will
total up the cubic feet for us.




Ron Rosenfeld replied on 23-May-07 02:03 PM
Set up a table. Name it something like CuFtTbl.

A	3.38
B	2.63
C	2.33
...
J

Then use this formula:

=VLOOKUP(CTN_SIZE,CuFtTbl,2,FALSE)
--ron
shawn replied on 23-May-07 03:03 PM
But then will not that table show up on printouts? Is there a way to put the
table on sheet2 and have it read  the data off that?
shawn replied on 23-May-07 03:07 PM
So far I have tried and cannot get it to work. I have tried to make a table below
my information on sheet1.
Peo Sjoblom replied on 23-May-07 03:07 PM
Yes, you can have the table in another sheet and even hide that sheet if you
want


--
Regards,

Peo Sjoblom
Bob I replied on 23-May-07 03:33 PM
If your "Letter" is in cell A1, then put the following in the Cell you
want the size to appear.

=HLOOKUP(A1,{"A","B","C";3.38,2.63,2.33},2)&" Cubic Feet"
shawn replied on 23-May-07 03:53 PM
Here's what I have currently..

Sheet1 has my Shipping Manifest.

Sheet2 has the following data from A1 to B11

BOX CF
A 3.38
B 2.63
C 2.33
D 2.04
E 1.56
F 1.04
G .65
H .41
I .41
J .25

I typed that data in, highlighted it all then converted it to a table.

I think where I am having trouble is the "lookup value"

I have written what Ron suggested and tried to go through the evaluate thing
to find errors.
Peo Sjoblom replied on 23-May-07 04:05 PM
You can use

=IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet2!$A$2:$B$11,2,0)),"Incorrect Value
Entered",VLOOKUP(A1,Sheet2!$A$2:$B$11,2,0)))


replace A1 with the cell where you input the size



or hard coded which means you don't need the table in Sheet2

=IF(A1="","",IF(ISNA(VLOOKUP(A1,{"A",3.38;"B",2.63;"C",2.33;"D",2.04;"E",1.56;"F",1.04;"G",0.65;"H",0.41;"I",0.41;"J",25},2,0)),"Incorrect
Value
Entered",VLOOKUP(A1,{"A",3.38;"B",2.63;"C",2.33;"D",2.04;"E",1.56;"F",1.04;"G",0.65;"H",0.41;"I",0.41;"J",25},2,0)))



replace A1 with the cell where you input the size





--
Regards,

Peo Sjoblom
shawn replied on 25-May-07 11:36 AM
Thanks. I used the hard coded method. Is there a way for this to also work
in Excel 97-2003 format? It's not a big deal if it won't work, but three
machines in our office aren't upgraded to Excel 2007.
Peo Sjoblom replied on 25-May-07 11:47 AM
The formula works fine in previous versions, you need to save the file in
xls format, do a save as after hitting the office button and you will se what
I mean


--
Regards,

Peo Sjoblom
Satwinder Saluja replied to shawn on 22-Apr-10 05:17 AM
How to calculate carton size in cubic ft.
Satwinder Saluja replied to shawn on 22-Apr-10 05:18 AM
How to calculate carton size in cubic ft.