I am using a spreadsheet to track print jobs. One of the things I track is
the type of paper used and the cost of each piece for each job. Each paper
stock is assigned a stock code and has a unique cost. I want to be able to
enter the code and have the cost of the paper automatically come up in the
next cell. I currently have 16 different codes and each one has a different
cost. For instance, I want to enter the code PS1 in cell K5 and want it to
put the cost associated with PS1 (.03) in cell L5. I have tried to use the IF
function, but you can only nest 7 in a cell (I think). Any ideas? Any help
would be appreciated. Thanks!

Set up a little table with PS1 in one cell, .03 in the next cell to the
right, then PS2 & .073 in the next row, etc, then use
=VLOOKUP(K5,x,2,FALSE)
where x is the reference to the 2 columns x ?rows, like G1:H16

"braveT" wrote in message
...
I am using a spreadsheet to track print jobs. One of the things I track is
the type of paper used and the cost of each piece for each job. Each paper
stock is assigned a stock code and has a unique cost. I want to be able to
enter the code and have the cost of the paper automatically come up in the
next cell. I currently have 16 different codes and each one has a
different
cost. For instance, I want to enter the code PS1 in cell K5 and want it to
put the cost associated with PS1 (.03) in cell L5. I have tried to use the
IF
function, but you can only nest 7 in a cell (I think). Any ideas? Any help
would be appreciated. Thanks!

How about using a vlookup formula? Going with what you said you have 16
different codes with different cost and let's say this data is in a worksheet
that is named "Cost". And let's say the data you have for this worksheet
begins in A1. So you have data from A1:A16 for the codes and B1:B16 for the
cost.

=VLOOKUP(k5,Cost!$A$1:$B$16,2,FALSE)

"braveT" wrote:

I am using a spreadsheet to track print jobs. One of the things I track is
the type of paper used and the cost of each piece for each job. Each paper
stock is assigned a stock code and has a unique cost. I want to be able to
enter the code and have the cost of the paper automatically come up in the
next cell. I currently have 16 different codes and each one has a different
cost. For instance, I want to enter the code PS1 in cell K5 and want it to
put the cost associated with PS1 (.03) in cell L5. I have tried to use the IF
function, but you can only nest 7 in a cell (I think). Any ideas? Any help
would be appreciated. Thanks!

Create a 2 column table. The left column will be the codes and the right
column will be the corresponding price.

Assume that table is in the range A1:B16

Then you can use one of these formulas to get the price:

K5 = PS1

=SUMIF(A1:A16,K15,B1:B16)

=IF(K15="",0,VLOOKUP(K15,A1:B16,2,0))

=IF(K15="",0,INDEX(B1:B16,MATCH(K15,A1:A16,0)))

Biff

"braveT" wrote in message
...
I am using a spreadsheet to track print jobs. One of the things I track is
the type of paper used and the cost of each piece for each job. Each paper
stock is assigned a stock code and has a unique cost. I want to be able to
enter the code and have the cost of the paper automatically come up in the
next cell. I currently have 16 different codes and each one has a
different
cost. For instance, I want to enter the code PS1 in cell K5 and want it to
put the cost associated with PS1 (.03) in cell L5. I have tried to use the
IF
function, but you can only nest 7 in a cell (I think). Any ideas? Any help
would be appreciated. Thanks!

It worked. I had tried the Vlookup before, but I did not have it formatted
correctly. Is there any way to hide the error message "#n/a" when I have not
entered the code yet?
Thanks for your help!

"Storm" wrote:

How about using a vlookup formula? Going with what you said you have 16
different codes with different cost and let's say this data is in a worksheet
that is named "Cost". And let's say the data you have for this worksheet
begins in A1. So you have data from A1:A16 for the codes and B1:B16 for the
cost.

=VLOOKUP(k5,Cost!$A$1:$B$16,2,FALSE)

"braveT" wrote:

I am using a spreadsheet to track print jobs. One of the things I track is
the type of paper used and the cost of each piece for each job. Each paper
stock is assigned a stock code and has a unique cost. I want to be able to
enter the code and have the cost of the paper automatically come up in the
next cell. I currently have 16 different codes and each one has a different
cost. For instance, I want to enter the code PS1 in cell K5 and want it to
put the cost associated with PS1 (.03) in cell L5. I have tried to use the IF
function, but you can only nest 7 in a cell (I think). Any ideas? Any help
would be appreciated. Thanks!

yes sir, there is a way.

=if(isblank(k5),"",vlookup(k5,Cost!$A$1:$B$16,2,FA LSE))

*where K5 is the cell where you put the code in.

-storm :)

"braveT" wrote:

It worked. I had tried the Vlookup before, but I did not have it formatted
correctly. Is there any way to hide the error message "#n/a" when I have not
entered the code yet?
Thanks for your help!

"Storm" wrote:

How about using a vlookup formula? Going with what you said you have 16
different codes with different cost and let's say this data is in a worksheet
that is named "Cost". And let's say the data you have for this worksheet
begins in A1. So you have data from A1:A16 for the codes and B1:B16 for the
cost.

=VLOOKUP(k5,Cost!$A$1:$B$16,2,FALSE)

"braveT" wrote:

I am using a spreadsheet to track print jobs. One of the things I track is
the type of paper used and the cost of each piece for each job. Each paper
stock is assigned a stock code and has a unique cost. I want to be able to
enter the code and have the cost of the paper automatically come up in the
next cell. I currently have 16 different codes and each one has a different
cost. For instance, I want to enter the code PS1 in cell K5 and want it to
put the cost associated with PS1 (.03) in cell L5. I have tried to use the IF
function, but you can only nest 7 in a cell (I think). Any ideas? Any help
would be appreciated. Thanks!