Microsoft Office Forums


Reply
Thread Tools Display Modes

Carton size and cubic feet auto calculate..

 
 
shawn
Guest
Posts: n/a
 
      05-23-2007, 05: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.



 
Reply With Quote
 
 
 
 
Ron Rosenfeld
Guest
Posts: n/a
 
      05-23-2007, 06:03 PM
On Wed, 23 May 2007 13:50:39 -0400, "shawn" <(E-Mail Removed)> wrote:

>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.
>
>


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
 
Reply With Quote
 
 
 
 
shawn
Guest
Posts: n/a
 
      05-23-2007, 07:03 PM
But then won't that table show up on printouts? Is there a way to put the
table on sheet2 and have it read the data off that?

"Ron Rosenfeld" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Wed, 23 May 2007 13:50:39 -0400, "shawn" <(E-Mail Removed)>
> wrote:
>
>>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.
>>
>>

>
> 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



 
Reply With Quote
 
shawn
Guest
Posts: n/a
 
      05-23-2007, 07:07 PM
So far I've tried and can't get it to work. I've tried to make a table below
my information on sheet1.

"Ron Rosenfeld" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Wed, 23 May 2007 13:50:39 -0400, "shawn" <(E-Mail Removed)>
> wrote:
>
>>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.
>>
>>

>
> 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



 
Reply With Quote
 
Peo Sjoblom
Guest
Posts: n/a
 
      05-23-2007, 07:07 PM
Yes, you can have the table in another sheet and even hide that sheet if you
want


--
Regards,

Peo Sjoblom



"shawn" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> But then won't that table show up on printouts? Is there a way to put the
> table on sheet2 and have it read the data off that?
>
> "Ron Rosenfeld" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> On Wed, 23 May 2007 13:50:39 -0400, "shawn" <(E-Mail Removed)>
>> wrote:
>>
>>>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.
>>>
>>>

>>
>> 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

>
>



 
Reply With Quote
 
Bob I
Guest
Posts: n/a
 
      05-23-2007, 07: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 wrote:

> But then won't that table show up on printouts? Is there a way to put the
> table on sheet2 and have it read the data off that?
>
> "Ron Rosenfeld" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>
>>On Wed, 23 May 2007 13:50:39 -0400, "shawn" <(E-Mail Removed)>
>>wrote:
>>
>>
>>>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.
>>>
>>>

>>
>>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

>
>
>


 
Reply With Quote
 
shawn
Guest
Posts: n/a
 
      05-23-2007, 07: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.


 
Reply With Quote
 
Peo Sjoblom
Guest
Posts: n/a
 
      05-23-2007, 08: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.4 1;"J",25},2,0)))



replace A1 with the cell where you input the size





--
Regards,

Peo Sjoblom

"shawn" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>



 
Reply With Quote
 
shawn
Guest
Posts: n/a
 
      05-25-2007, 03:36 PM
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.4 1;"J",25},2,0)))
>
>
>
> replace A1 with the cell where you input the size
>
>
>
>
>
> --
> Regards,
>
> Peo Sjoblom
>
> "shawn" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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.
>>

>
>



 
Reply With Quote
 
Peo Sjoblom
Guest
Posts: n/a
 
      05-25-2007, 03:47 PM
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'll se what
I mean


--
Regards,

Peo Sjoblom



"shawn" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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.4 1;"J",25},2,0)))
>>
>>
>>
>> replace A1 with the cell where you input the size
>>
>>
>>
>>
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>> "shawn" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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.
>>>

>>
>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003, how to convert gallons to cubic feet? Petec Excel Worksheets 6 03-03-2009 02:53 AM
Cubic Parabola or Cubic Spiral Michael M Excel Worksheets 0 08-07-2006 10:19 PM
Convert cubic centimeters to fluid ounces and cubic centimeters OcalaElaine Excel Worksheets 4 06-14-2006 05:43 PM
convert cubic centimeters to fluid ounces and cubic centimeters OcalaElaine Excel Worksheets 4 06-13-2006 09:03 PM


All times are GMT. The time now is 03:08 AM.
Microsoft Office Forums is not affiliated with Microsoft Corporation.


Welcome!
Welcome to the Microsoft Office Forums
 


Latest Threads
New user looking for shapes/templates
SargeUSMC (04-21-2014, 04:23 PM)

Mail Merging Content With High Quality
oleander (04-17-2014, 11:59 PM)

Aargh. What is this?
PeteJ (04-17-2014, 12:10 PM)

Word 2013 Insert Symbol (Not Responding)
tpthrshr (04-17-2014, 02:49 AM)

Can't save a large Word document
Josh (04-17-2014, 01:03 AM)