further to my earlier questions

cocosoft · May 15th, 2002, 06:16 PM

Hi guys & thanks to all who have helped me with my ongoing project thus far.

I have done a combined Quote, invoice, materials ordering, and cumulative tax (VAT) spreadsheet for a mate, he has a fencing company. It's one of the most complex things of this nature that I have ever tackled, but by biting off little chunks and with your help it's all starting to come together.

My current problem for all you brainiacs goes something like this.
At first contact my mate usually gives a verbal price per metre for a specific fence type over the phone or face to face, then if there is interest expressed he does up an official quote using this spreadsheet I have written him.

He builds several different typesof fence. His only variable with which he can adjust the metre price to the customer is his labor rate per metre, currently in sheet 2 Cell (L2).
The mate adjusts his labor figure in small increments until the price per metre to the customer Sheet1 Cell (I25) is at at or just under the metre price he has quoted the customer verbally. This is tedious and he wishes to be able to punch in the price he quoted the customer per metre (I would be using sheet 2 Cell (J2) ) for that, and have a formula that adjusted his labor cell for him until Sheet1 Cell (I25) was equal to or marginally under sheet 2 Cell (J2) but prefferably under.

All my attempts to write something which incremented or decremented the price in the labor cell (L2) to give me an acceptable figure in the customers price per metre cell, Sheet1 Cell (I25) have resulted in circular reference errors.

Many formulas throughout this spreadsheet refer to sheet 2 Cell (L2). Ideally what I need is formula which produces an amount in another cell which increments or decrements as necessary to accomplish my desired result and which i can just have the labor cell (L2) incorporate or alternately some way to increment/decrement the the amount in (L2) in small steps of .1 of a cent until Sheet1 Cell (I25) is eaqual to or just a fraction under sheet 2 Cell (J2)

At present we manipulate the labor price in (L2) but if you can help me with this, (L2) would be automatically adjusted thereafter according to whatever was typed into the desired price in Sheet 2 (J2)

Honestly I'm so peed off with this one that I've thrown out all my attempted formulas and thought one of you guys will just laugh and say OK stupid, here'e your answer. There ain't nothing wrong with that if it saves me pulling put more hair.
How do I do it & thanks in advance to anyone who has a go at this one.

labor rate per metre, currently in sheet 2 Cell (L2) normal rannge is between $8 and $20 depending on fence type
Price per metre to the customer Sheet1 Cell (I25) which he will see on his quote by way of confirmation of verbal quote
Price Verbally quoted to customer sheet 2 Cell (J2)
Any formulas you come up with could reside in Sheet2 (J3 to J24)

Due to ill health I'm not on line all that much except for the odd day when i'm feeling better than usual however i will answer any questions as promptly as i can. Sorry for the length of this post.

Al Chara · May 15th, 2002, 07:05 PM

What is your formula for computing the price per meter?

This can be done pretty easily with a little help from some VBA.

Have you taken a look at Excel's Solver Add-in? It should also do what you want.

Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-05-15 18:12 ]

Yogi Anand · May 15th, 2002, 08:08 PM

Have you looked into using GoalSeek --
looks like that should help out in this case.

Regards!

[ This Message was edited by: Yogi Anand on 2002-05-17 06:57 ]

cocosoft · May 15th, 2002, 11:37 PM

Formula for Price per metre follows, hope it helps in some way.
=IF(C9>0,(SUM(Sheet2!Q3,Sheet2!Q4,Sheet2!Q5,Sheet2!Q6,Sheet2!Q14,Sheet2!Q15)/C9)+(SUM(Sheet2!Q3,Sheet2!Q4,Sheet2!Q5,Sheet2!Q6,Sheet2!Q14,Sheet2!Q15)/C9)*10%,0)

Thanx for the assist Al. .. Coco
On 2002-05-15 18:05, Al Chara wrote:
What is your formula for computing the price per meter?

This can be done pretty easily with a little help from some VBA.

Have you taken a look at Excel's Solver Add-in? It should also do what you want.

Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-05-15 18:12 ]
[/quote]

cocosoft · May 15th, 2002, 11:44 PM

To Yogi & Al Hey guys thanks again for offering assistance.
The things you have mentioned Excel's Solver Add-in
GolaSeek -- I am in no way familiar with.
What are they and where can I see/download/access them???.... Coco

On 2002-05-15 22:37, cocosoft wrote:
Formula for Price per metre follows, hope it helps in some way.
=IF(C9>0,(SUM(Sheet2!Q3,Sheet2!Q4,Sheet2!Q5,Sheet2!Q6,Sheet2!Q14,Sheet2!Q15)/C9)+(SUM(Sheet2!Q3,Sheet2!Q4,Sheet2!Q5,Sheet2!Q6,Sheet2!Q14,Sheet2!Q15)/C9)*10%,0)

Thanx for the assist Al. .. Coco
On 2002-05-15 18:05, Al Chara wrote:
What is your formula for computing the price per meter?

This can be done pretty easily with a little help from some VBA.

Have you taken a look at Excel's Solver Add-in? It should also do what you want.

Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-05-15 18:12 ]
[/quote]

[/quote]

Al Chara · May 16th, 2002, 08:42 AM

cocosoft,

I think Yogi mean't GoalSeek. GoalSeek is a VBA method. From the help file:

Quote:

GoalSeek Method

Calculates the values necessary to achieve a specific goal. If the goal is an amount returned by a formula, this calculates a value that, when supplied to your formula, causes the formula to return the number you want. Returns True if the goal seek is successful.

expression.GoalSeek(Goal, ChangingCell)

expression Required. An expression that returns a Range object. Must be a single cell.

Goal: Required Variant. The value you want returned in this cell.

ChangingCell: Required Range. Specifies which cell should be changed to achieve the target value.

Are you familiar with VBA and do you have a problem using it in this case?

cocosoft · May 16th, 2002, 03:37 PM

No Al I'm not familiar with VBA but what you suggest here appears to be EXACTLY what I need in this situation.
Given the cell references I have previously stated as follows, could you help me further with this. Appears to me, using Goal Seek that we only need to refer to cells below, given my prior explanations.
I will be reading up on GoalSeek today as prior to yesterday I had not ever heard of it.
This constant learning gives one a bit of a headache at 50 years old mate. Cheers Coco.

On 2002-05-16 07:42, Al Chara wrote:
cocosoft,

I think Yogi mean't GoalSeek. GoalSeek is a VBA method. From the help file:

Quote:

GoalSeek Method

Calculates the values necessary to achieve a specific goal. If the goal is an amount returned by a formula, this calculates a value that, when supplied to your formula, causes the formula to return the number you want. Returns True if the goal seek is successful.

expression.GoalSeek(Goal, ChangingCell)

expression Required. An expression that returns a Range object. Must be a single cell.

Goal: Required Variant. The value you want returned in this cell.

ChangingCell: Required Range. Specifies which cell should be changed to achieve the target value.

Are you familiar with VBA and do you have a problem using it in this case?

[/quote]

Al Chara · May 16th, 2002, 07:22 PM

The following should be the correct form for you:

Worksheets("Sheet1").Range("I25").GoalSeek _
Goal:=Worksheets("Sheet2").Range("J2"), _
ChangingCell:=Worksheets("Sheet2").Range("L2")

Yogi Anand · May 16th, 2002, 08:36 PM

Hi cocosoft and Al:
Goal Seek capability is also available in the worksheet environment. Look at TOOLS|GOAL SEEK from the worksheet menubar

Regards!

cocosoft · May 17th, 2002, 11:09 PM

Reply to both Al and Yogi.
Back at you guys & thanks again for your help.
I have made myself familiar with Goal Seeking and it does solve my problem however for my client I have been trying to get a macro to call Goal Seek and insert all the vell references and refer to a specific cell for the variable. however I've been totally unable to get a macro to specifically refer to a cell where the variable is, neither will a macro calling Goal seek allow me to paste in a variable copied when the macro is first called.
Do either one of you understand what I'm trying to say and know a way around it.
If I can get past this one I will have almost finished this project and it will be a great relief. thanx again for your efforts Guys. Cheers.. Coco

On 2002-05-16 19:36, Yogi Anand wrote:
Hi cocosoft and Al:
Goal Seek capability is also available in the worksheet environment. Look at TOOLS|GOAL SEEK from the worksheet menubar

Regards!
[/quote]

May 16th, 2002, 07:22 PM	#8
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	The following should be the correct form for you: Worksheets("Sheet1").Range("I25").GoalSeek _ Goal:=Worksheets("Sheet2").Range("J2"), _ ChangingCell:=Worksheets("Sheet2").Range("L2") __________________ Kind regards, Al Chara

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 15th, 2002, 06:16 PM	#1
cocosoft New Member Join Date: May 2002 Location: Australia Posts: 31	Hi guys & thanks to all who have helped me with my ongoing project thus far. I have done a combined Quote, invoice, materials ordering, and cumulative tax (VAT) spreadsheet for a mate, he has a fencing company. It's one of the most complex things of this nature that I have ever tackled, but by biting off little chunks and with your help it's all starting to come together. My current problem for all you brainiacs goes something like this. At first contact my mate usually gives a verbal price per metre for a specific fence type over the phone or face to face, then if there is interest expressed he does up an official quote using this spreadsheet I have written him. He builds several different typesof fence. His only variable with which he can adjust the metre price to the customer is his labor rate per metre, currently in sheet 2 Cell (L2). The mate adjusts his labor figure in small increments until the price per metre to the customer Sheet1 Cell (I25) is at at or just under the metre price he has quoted the customer verbally. This is tedious and he wishes to be able to punch in the price he quoted the customer per metre (I would be using sheet 2 Cell (J2) ) for that, and have a formula that adjusted his labor cell for him until Sheet1 Cell (I25) was equal to or marginally under sheet 2 Cell (J2) but prefferably under. All my attempts to write something which incremented or decremented the price in the labor cell (L2) to give me an acceptable figure in the customers price per metre cell, Sheet1 Cell (I25) have resulted in circular reference errors. Many formulas throughout this spreadsheet refer to sheet 2 Cell (L2). Ideally what I need is formula which produces an amount in another cell which increments or decrements as necessary to accomplish my desired result and which i can just have the labor cell (L2) incorporate or alternately some way to increment/decrement the the amount in (L2) in small steps of .1 of a cent until Sheet1 Cell (I25) is eaqual to or just a fraction under sheet 2 Cell (J2) At present we manipulate the labor price in (L2) but if you can help me with this, (L2) would be automatically adjusted thereafter according to whatever was typed into the desired price in Sheet 2 (J2) Honestly I'm so peed off with this one that I've thrown out all my attempted formulas and thought one of you guys will just laugh and say OK stupid, here'e your answer. There ain't nothing wrong with that if it saves me pulling put more hair. How do I do it & thanks in advance to anyone who has a go at this one. labor rate per metre, currently in sheet 2 Cell (L2) normal rannge is between $8 and $20 depending on fence type Price per metre to the customer Sheet1 Cell (I25) which he will see on his quote by way of confirmation of verbal quote Price Verbally quoted to customer sheet 2 Cell (J2) Any formulas you come up with could reside in Sheet2 (J3 to J24) Due to ill health I'm not on line all that much except for the odd day when i'm feeling better than usual however i will answer any questions as promptly as i can. Sorry for the length of this post.

May 15th, 2002, 07:05 PM	#2
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	What is your formula for computing the price per meter? This can be done pretty easily with a little help from some VBA. Have you taken a look at Excel's Solver Add-in? It should also do what you want. Hope this helps. Kind regards, Al. [ This Message was edited by: Al Chara on 2002-05-15 18:12 ]

May 15th, 2002, 08:08 PM	#3
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Have you looked into using GoalSeek -- looks like that should help out in this case. Regards! [ This Message was edited by: Yogi Anand on 2002-05-17 06:57 ]

May 15th, 2002, 11:37 PM	#4
cocosoft New Member Join Date: May 2002 Location: Australia Posts: 31	Formula for Price per metre follows, hope it helps in some way. =IF(C9>0,(SUM(Sheet2!Q3,Sheet2!Q4,Sheet2!Q5,Sheet2!Q6,Sheet2!Q14,Sheet2!Q15)/C9)+(SUM(Sheet2!Q3,Sheet2!Q4,Sheet2!Q5,Sheet2!Q6,Sheet2!Q14,Sheet2!Q15)/C9)*10%,0) Thanx for the assist Al. .. Coco On 2002-05-15 18:05, Al Chara wrote: What is your formula for computing the price per meter? This can be done pretty easily with a little help from some VBA. Have you taken a look at Excel's Solver Add-in? It should also do what you want. Hope this helps. Kind regards, Al. [ This Message was edited by: Al Chara on 2002-05-15 18:12 ] [/quote]

May 15th, 2002, 11:44 PM	#5
cocosoft New Member Join Date: May 2002 Location: Australia Posts: 31	To Yogi & Al Hey guys thanks again for offering assistance. The things you have mentioned Excel's Solver Add-in GolaSeek -- I am in no way familiar with. What are they and where can I see/download/access them???.... Coco On 2002-05-15 22:37, cocosoft wrote: Formula for Price per metre follows, hope it helps in some way. =IF(C9>0,(SUM(Sheet2!Q3,Sheet2!Q4,Sheet2!Q5,Sheet2!Q6,Sheet2!Q14,Sheet2!Q15)/C9)+(SUM(Sheet2!Q3,Sheet2!Q4,Sheet2!Q5,Sheet2!Q6,Sheet2!Q14,Sheet2!Q15)/C9)*10%,0) Thanx for the assist Al. .. Coco On 2002-05-15 18:05, Al Chara wrote: What is your formula for computing the price per meter? This can be done pretty easily with a little help from some VBA. Have you taken a look at Excel's Solver Add-in? It should also do what you want. Hope this helps. Kind regards, Al. [ This Message was edited by: Al Chara on 2002-05-15 18:12 ] [/quote] [/quote]

May 16th, 2002, 08:36 PM	#9
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi cocosoft and Al: Goal Seek capability is also available in the worksheet environment. Look at TOOLS\|GOAL SEEK from the worksheet menubar Regards!

May 17th, 2002, 11:09 PM	#10
cocosoft New Member Join Date: May 2002 Location: Australia Posts: 31	Reply to both Al and Yogi. Back at you guys & thanks again for your help. I have made myself familiar with Goal Seeking and it does solve my problem however for my client I have been trying to get a macro to call Goal Seek and insert all the vell references and refer to a specific cell for the variable. however I've been totally unable to get a macro to specifically refer to a cell where the variable is, neither will a macro calling Goal seek allow me to paste in a variable copied when the macro is first called. Do either one of you understand what I'm trying to say and know a way around it. If I can get past this one I will have almost finished this project and it will be a great relief. thanx again for your efforts Guys. Cheers.. Coco On 2002-05-16 19:36, Yogi Anand wrote: Hi cocosoft and Al: Goal Seek capability is also available in the worksheet environment. Look at TOOLS\|GOAL SEEK from the worksheet menubar Regards! [/quote]