Button for goal seek?!

[Neo1]

Hello I have the following data:

5
6
12
15
20
Total of all those added

I will have to set a goal seek button so that i can put the total i
want and then change the cell i want i tried using a macro but i
couldnt do it... I just want to do it using a click of a button instead
of going to menu>goal seek>etc etc


Thanks a lot
From John

 

[Neo1]

Any Help pleasee, would be appreciated..

Thanks
From John

 

[Neo1]

Any Help pleasee, would be appreciated..

Thanks
From John

 

[Alex]

Open your spreadsheet and add this into a module:

Sub Macro1()
Dim intGoal
intGoal = InputBox("Enter the goal you want")

Range("E6").GoalSeek Goal:=intGoal, ChangingCell:=Range("E5")
End Sub

Change the two ranges (here E6 and E5) to your source and destination
cells.

To make a button for this, go to view->toolbars->customize

Scroll down to macros

Drag an empty button to the toolbar of your choice

With the dialog box still open, right click on the button you just
added and select "assign macro".

Enjoy!

Alex.

 

[Neo1]

It doesnt seem to work....when i click the button it just points at the
goal seek cell the one that is the total of all the other numbers....

Isnt there another way without doing an coding?

Thanks
From John

 

[Neo1]

What am i meant to put for the ("Enter the goal you want") maybe its
something to do with that..which im having a problem

But isnt there another easier way pehaps?

Thanks for your time
From John

 

[Alex]

Ok, what exactly is the question?

You have this data:

5
6
12
15
20

The total of these is 58

Let us assume that you put this total at the bottom of your range:

5
6
12
15
20
58

The purpose of goal seek is to change a cell with formulas in it to a
specific value by changing another value that the formula points to.
In the case you gave, this would be like saying that you want the total
(currently 58) to be 70 by changing the SPECIFIC value 12 in your list.


In this case, goal seek would recommend that the 12 be changed to 24.
The value 12 would be changed to 24 in your list, making the total 70.
Is this what you want done?

If so, what I described will work.

Alternatly, you could make the last value in your list be the catch-all
to give the total you want. The cell at the bottom of your list would
be (assuming your list starts at A1):

5
6
12
15
20
= a7 - sum(a1:a5)
= (value of the total you want)

How about that? You only key in one value at the end and it makes the
total that number.

Alex.