Hello, I am VERY new to Excel. I have created a pricing worksheet for a
printing company. With printing we will have a lot of extra services
depending on the job, what I would like to do is be able to enter a code
number that will provide a certain formula from a list of formulas need for
the extra services. For Example:

On one job I would enter "A1S" and the "B5E"

Extra Code A1S
Extra Code B5E

There will be a list:

A1S - 1 position score - with a formula =20+.02* quantity
B5E - Blank A2 Envelopes - with a formula =.15* quantity

The list can be coded anyway

Any ideas? Thanks for your help.

Keith

You could do it with a vlookup, put in the code and the cell next to it would
show the formula. The best way however would be through a user defined
function, you would type like =myFunc(A1,A1S) and it would perform the A1S
calculation on cell A1.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"TradeBind" wrote:

Hello, I am VERY new to Excel. I have created a pricing worksheet for a
printing company. With printing we will have a lot of extra services
depending on the job, what I would like to do is be able to enter a code
number that will provide a certain formula from a list of formulas need for
the extra services. For Example:

On one job I would enter "A1S" and the "B5E"

Extra Code A1S
Extra Code B5E

There will be a list:

A1S - 1 position score - with a formula =20+.02* quantity
B5E - Blank A2 Envelopes - with a formula =.15* quantity

The list can be coded anyway

Any ideas? Thanks for your help.

Keith

Thanks for the reply John, so I will need to type in a myFunc formula
"=myFunc(A1,A1S)" everytime I am doing a new work sheet? Which is OK I guess
isn't there a way I can use the code and it refers to the name in say A1 and
the formula is B1?

Thanks,
Keith

"John Bundy" wrote:

You could do it with a vlookup, put in the code and the cell next to it would
show the formula. The best way however would be through a user defined
function, you would type like =myFunc(A1,A1S) and it would perform the A1S
calculation on cell A1.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"TradeBind" wrote:

Hello, I am VERY new to Excel. I have created a pricing worksheet for a
printing company. With printing we will have a lot of extra services
depending on the job, what I would like to do is be able to enter a code
number that will provide a certain formula from a list of formulas need for
the extra services. For Example:

On one job I would enter "A1S" and the "B5E"

Extra Code A1S
Extra Code B5E

There will be a list:

A1S - 1 position score - with a formula =20+.02* quantity
B5E - Blank A2 Envelopes - with a formula =.15* quantity

The list can be coded anyway

Any ideas? Thanks for your help.

Keith

Not sure I am following what you are doing. Where are the formulas stored?
where is the value you are performing the formula on? and how do you know
which formula to use (where is it)?
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"TradeBind" wrote:

Thanks for the reply John, so I will need to type in a myFunc formula
"=myFunc(A1,A1S)" everytime I am doing a new work sheet? Which is OK I guess
isn't there a way I can use the code and it refers to the name in say A1 and
the formula is B1?

Thanks,
Keith

"John Bundy" wrote:

You could do it with a vlookup, put in the code and the cell next to it would
show the formula. The best way however would be through a user defined
function, you would type like =myFunc(A1,A1S) and it would perform the A1S
calculation on cell A1.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"TradeBind" wrote:

Hello, I am VERY new to Excel. I have created a pricing worksheet for a
printing company. With printing we will have a lot of extra services
depending on the job, what I would like to do is be able to enter a code
number that will provide a certain formula from a list of formulas need for
the extra services. For Example:

On one job I would enter "A1S" and the "B5E"

Extra Code A1S
Extra Code B5E

There will be a list:

A1S - 1 position score - with a formula =20+.02* quantity
B5E - Blank A2 Envelopes - with a formula =.15* quantity

The list can be coded anyway

Any ideas? Thanks for your help.

Keith

Sorry for being so Excel challenged John! I am sure it is much simplier than
I am making it. I have a list:
Quantity 1000
# of colors side 1 1
# of colors side 2 1
Flat Size Length 3.50
Flat Size Width 2.00
Die sq. in. 7.00
Die Cost/sqin $00.00
Admin Cost $00.00
Die Mark up $00.05
Die Sub Total $00.30

Paper sq. In. 7.00
Paper cost $00.00
Plate Hang Cost $00.00
Run Time Cost $00.00
Trim Cost $00.00
EXTRA CODE ___ $00.00
EXTRA CODE___ $00.00
Sub Total $000.00
Mark-up $00.00
TOTAL $000.00

I guess I will need to have a place to enter the code and then it will add
to the total.
All I really want is a way to enter extra items without getting into writing
formulas everytime. The codes and there formulas can be in the same worksheet
because it won't be that hugh of list.

Thanks,
Keith



"John Bundy" wrote:

Not sure I am following what you are doing. Where are the formulas stored?
where is the value you are performing the formula on? and how do you know
which formula to use (where is it)?
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"TradeBind" wrote:

Thanks for the reply John, so I will need to type in a myFunc formula
"=myFunc(A1,A1S)" everytime I am doing a new work sheet? Which is OK I guess
isn't there a way I can use the code and it refers to the name in say A1 and
the formula is B1?

Thanks,
Keith

"John Bundy" wrote:

You could do it with a vlookup, put in the code and the cell next to it would
show the formula. The best way however would be through a user defined
function, you would type like =myFunc(A1,A1S) and it would perform the A1S
calculation on cell A1.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"TradeBind" wrote:

Hello, I am VERY new to Excel. I have created a pricing worksheet for a
printing company. With printing we will have a lot of extra services
depending on the job, what I would like to do is be able to enter a code
number that will provide a certain formula from a list of formulas need for
the extra services. For Example:

On one job I would enter "A1S" and the "B5E"

Extra Code A1S
Extra Code B5E

There will be a list:

A1S - 1 position score - with a formula =20+.02* quantity
B5E - Blank A2 Envelopes - with a formula =.15* quantity

The list can be coded anyway

Any ideas? Thanks for your help.

Keith

You can use a lookup table that has a flat rate + % of quantity, so
long as quantity stays put or is in a named cell.

Set up the list like this, on a new sheet called ExtraCodes, starting
in A1:

CODE Description Flat % Qty
A1S 1 position score 20 0.2
B5E Blank A2 Envelopes 0 0.15

The list range can be addressed as follows:

'ExtraCodes'!$A$2:$D$3

If it extends below row 3, adjust it accordingly.

So for example, if your quantity value is in B3 of your main sheet,
and the codes are in A18 and A19, here is the formula:

A18 ="A1S"
B18 =VLOOKUP(A18,'ExtraCodes'!$A$2:$D$3,2,FALSE)
C18 =VLOOKUP(A18,'ExtraCodes'!$A$2:$D$3,3,FALSE)
+B3*VLOOKUP(A18,'ExtraCodes'!$A$2:$D$3,3,FALSE)

All you have to do is input the Extra Code in A18 - column B will
return the description and column C will have the total amount, based
on extra code and quantity.

See the help for VLOOKUP worksheet function for more information on
how this works.

On Jan 9, 11:43 am, TradeBind
wrote:
Sorry for being so Excel challenged John! I am sure it is much simplier than
I am making it. I have a list:
Quantity 1000
# of colors side 1 1
# of colors side 2 1
Flat Size Length 3.50
Flat Size Width 2.00
Die sq. in. 7.00
Die Cost/sqin $00.00
Admin Cost $00.00
Die Mark up $00.05
Die Sub Total $00.30

Paper sq. In. 7.00
Paper cost $00.00
Plate Hang Cost $00.00
Run Time Cost $00.00
Trim Cost $00.00
EXTRA CODE ___ $00.00
EXTRA CODE___ $00.00
Sub Total $000.00
Mark-up $00.00
TOTAL $000.00

I guess I will need to have a place to enter the code and then it will add
to the total.
All I really want is a way to enter extra items without getting into writing
formulas everytime. The codes and there formulas can be in the same worksheet
because it won't be that hugh of list.

Thanks,
Keith

"John Bundy" wrote:
Not sure I am following what you are doing. Where are the formulas stored?
where is the value you are performing the formula on? and how do you know
which formula to use (where is it)?
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"TradeBind" wrote:

Thanks for the reply John, so I will need to type in a myFunc formula
"=myFunc(A1,A1S)" everytime I am doing a new work sheet? Which is OK I guess
isn't there a way I can use the code and it refers to the name in say A1 and
the formula is B1?

Thanks,
Keith

"John Bundy" wrote:

You could do it with a vlookup, put in the code and the cell next to it would
show the formula. The best way however would be through a user defined
function, you would type like =myFunc(A1,A1S) and it would perform the A1S
calculation on cell A1.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"TradeBind" wrote:

Hello, I am VERY new to Excel. I have created a pricing worksheet for a
printing company. With printing we will have a lot of extra services
depending on the job, what I would like to do is be able to enter a code
number that will provide a certain formula from a list of formulas need for
the extra services. For Example:

On one job I would enter "A1S" and the "B5E"

Extra Code A1S
Extra Code B5E

There will be a list:

A1S - 1 position score - with a formula =20+.02* quantity
B5E - Blank A2 Envelopes - with a formula =.15* quantity

The list can be coded anyway

Any ideas? Thanks for your help.

Keith

Correction:

A18 ="A1S"
B18 =VLOOKUP(A18,'ExtraCodes'!$A$2:$D$3,2,FALSE)
C18 =VLOOKUP(A18,'ExtraCodes'!$A$2:$D$3,3,FALSE)
+B3*VLOOKUP(A18,'ExtraCodes'!$A$2:$D$3,4,FALSE)

On Jan 9, 3:07 pm, iliace wrote:
You can use a lookup table that has a flat rate + % of quantity, so
long as quantity stays put or is in a named cell.

Set up the list like this, on a new sheet called ExtraCodes, starting
in A1:

CODE Description Flat % Qty
A1S 1 position score 20 0.2
B5E Blank A2 Envelopes 0 0.15

The list range can be addressed as follows:

'ExtraCodes'!$A$2:$D$3

If it extends below row 3, adjust it accordingly.

So for example, if your quantity value is in B3 of your main sheet,
and the codes are in A18 and A19, here is the formula:

A18 ="A1S"
B18 =VLOOKUP(A18,'ExtraCodes'!$A$2:$D$3,2,FALSE)
C18 =VLOOKUP(A18,'ExtraCodes'!$A$2:$D$3,3,FALSE)
+B3*VLOOKUP(A18,'ExtraCodes'!$A$2:$D$3,3,FALSE)

All you have to do is input the Extra Code in A18 - column B will
return the description and column C will have the total amount, based
on extra code and quantity.

See the help for VLOOKUP worksheet function for more information on
how this works.

On Jan 9, 11:43 am, TradeBind
wrote:

Sorry for being so Excel challenged John! I am sure it is much simplier than
I am making it. I have a list:
Quantity 1000
# of colors side 1 1
# of colors side 2 1
Flat Size Length 3.50
Flat Size Width 2.00
Die sq. in. 7.00
Die Cost/sqin $00.00
Admin Cost $00.00
Die Mark up $00.05
Die Sub Total $00.30

Paper sq. In. 7.00
Paper cost $00.00
Plate Hang Cost $00.00
Run Time Cost $00.00
Trim Cost $00.00
EXTRA CODE ___ $00.00
EXTRA CODE___ $00.00
Sub Total $000.00
Mark-up $00.00
TOTAL $000.00

I guess I will need to have a place to enter the code and then it will add
to the total.
All I really want is a way to enter extra items without getting into writing
formulas everytime. The codes and there formulas can be in the same worksheet
because it won't be that hugh of list.

Thanks,
Keith

"John Bundy" wrote:
Not sure I am following what you are doing. Where are the formulas stored?
where is the value you are performing the formula on? and how do you know
which formula to use (where is it)?
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"TradeBind" wrote:

Thanks for the reply John, so I will need to type in a myFunc formula
"=myFunc(A1,A1S)" everytime I am doing a new work sheet? Which is OK I guess
isn't there a way I can use the code and it refers to the name in say A1 and
the formula is B1?

Thanks,
Keith

"John Bundy" wrote:

You could do it with a vlookup, put in the code and the cell next to it would
show the formula. The best way however would be through a user defined
function, you would type like =myFunc(A1,A1S) and it would perform the A1S
calculation on cell A1.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"TradeBind" wrote:

Hello, I am VERY new to Excel. I have created a pricing worksheet for a
printing company. With printing we will have a lot of extra services
depending on the job, what I would like to do is be able to enter a code
number that will provide a certain formula from a list of formulas need for
the extra services. For Example:

On one job I would enter "A1S" and the "B5E"

Extra Code A1S
Extra Code B5E

There will be a list:

A1S - 1 position score - with a formula =20+.02* quantity
B5E - Blank A2 Envelopes - with a formula =.15* quantity

The list can be coded anyway

Any ideas? Thanks for your help.

Keith

Thanks Iliace, I think I'm getting close. One area I'm confused with is how
to set up the ExtraCodes sheet. So do I need a formula in A1 or do I just
have text?

Thanks for all your help.



"iliace" wrote:

Correction:

A18 ="A1S"
B18 =VLOOKUP(A18,'ExtraCodes'!$A$2:$D$3,2,FALSE)
C18 =VLOOKUP(A18,'ExtraCodes'!$A$2:$D$3,3,FALSE)
+B3*VLOOKUP(A18,'ExtraCodes'!$A$2:$D$3,4,FALSE)

On Jan 9, 3:07 pm, iliace wrote:
You can use a lookup table that has a flat rate + % of quantity, so
long as quantity stays put or is in a named cell.

Set up the list like this, on a new sheet called ExtraCodes, starting
in A1:

CODE Description Flat % Qty
A1S 1 position score 20 0.2
B5E Blank A2 Envelopes 0 0.15

The list range can be addressed as follows:

'ExtraCodes'!$A$2:$D$3

If it extends below row 3, adjust it accordingly.

So for example, if your quantity value is in B3 of your main sheet,
and the codes are in A18 and A19, here is the formula:

A18 ="A1S"
B18 =VLOOKUP(A18,'ExtraCodes'!$A$2:$D$3,2,FALSE)
C18 =VLOOKUP(A18,'ExtraCodes'!$A$2:$D$3,3,FALSE)
+B3*VLOOKUP(A18,'ExtraCodes'!$A$2:$D$3,3,FALSE)

All you have to do is input the Extra Code in A18 - column B will
return the description and column C will have the total amount, based
on extra code and quantity.

See the help for VLOOKUP worksheet function for more information on
how this works.

On Jan 9, 11:43 am, TradeBind
wrote:

Sorry for being so Excel challenged John! I am sure it is much simplier than
I am making it. I have a list:
Quantity 1000
# of colors side 1 1
# of colors side 2 1
Flat Size Length 3.50
Flat Size Width 2.00
Die sq. in. 7.00
Die Cost/sqin $00.00
Admin Cost $00.00
Die Mark up $00.05
Die Sub Total $00.30

Paper sq. In. 7.00
Paper cost $00.00
Plate Hang Cost $00.00
Run Time Cost $00.00
Trim Cost $00.00
EXTRA CODE ___ $00.00
EXTRA CODE___ $00.00
Sub Total $000.00
Mark-up $00.00
TOTAL $000.00

I guess I will need to have a place to enter the code and then it will add
to the total.
All I really want is a way to enter extra items without getting into writing
formulas everytime. The codes and there formulas can be in the same worksheet
because it won't be that hugh of list.

Thanks,
Keith

"John Bundy" wrote:
Not sure I am following what you are doing. Where are the formulas stored?
where is the value you are performing the formula on? and how do you know
which formula to use (where is it)?
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"TradeBind" wrote:

Thanks for the reply John, so I will need to type in a myFunc formula
"=myFunc(A1,A1S)" everytime I am doing a new work sheet? Which is OK I guess
isn't there a way I can use the code and it refers to the name in say A1 and
the formula is B1?

Thanks,
Keith

"John Bundy" wrote:

You could do it with a vlookup, put in the code and the cell next to it would
show the formula. The best way however would be through a user defined
function, you would type like =myFunc(A1,A1S) and it would perform the A1S
calculation on cell A1.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"TradeBind" wrote:

Hello, I am VERY new to Excel. I have created a pricing worksheet for a
printing company. With printing we will have a lot of extra services
depending on the job, what I would like to do is be able to enter a code
number that will provide a certain formula from a list of formulas need for
the extra services. For Example:

On one job I would enter "A1S" and the "B5E"

Extra Code A1S
Extra Code B5E

There will be a list:

A1S - 1 position score - with a formula =20+.02* quantity
B5E - Blank A2 Envelopes - with a formula =.15* quantity

The list can be coded anyway

Any ideas? Thanks for your help.

Keith