Variable codes in Excel
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 |
Variable codes in Excel
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 |
Variable codes in Excel
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 |
Variable codes in Excel
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 |
Variable codes in Excel
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 |
Variable codes in Excel
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 |
Variable codes in Excel
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 |
Variable codes in Excel
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 |
All times are GMT +1. The time now is 03:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com