Output result to another cell 3

[jmw]

In Excel 2002, how do I get a formula to put its result into another cell?

A1 sheet 1 is unprotected and contains no formula and the user may write to it.
This value may be used in a calculation.
But if he chooses an alternative calculation I want the calculation result written here.

Hence on sheet 2 (where I do the calculations and then hide the sheet):
B1 contains a 1 or a 2
C1 contains a value
D1 contains a formula that looks at B1 and says that If B1=1, do nothing (the value in A1 sheet 1 will be used in another calculation) but if B1=2 copy the value from C1 sheet 2 into A1 on sheet 1 overwriting the cell contents.

Seems a simple enough objective, and very useful, I'm sure, but it doesn't seem to be one of Excel's attributes.
Any ideas how to do it?

JMW

http://www.ViscoAnalyser.com

 

[MintJulep]

You don't. At least not with native functions.

A formula in a cell cannot alter the contents of another cell.

It could be achieved with VBA. But I would suggest rethinking the arrangement.

 

[jmw]

Well, some googling has come up with a way to do part of this.
By clicking the sheet tab and view code I can write:

Private Sub Worksheet_Calculate()
Range("C11") = Range("C10")
End Sub

And so long as C10 contains a formula, it works.
I can write what I like in C11 but if I cause C11 to update then it overwrites the answer to C10.

But I now need to include an IF statement so it only does this if another cell, C12, say, has a 1 in it but not if it has a 2.
I can write If statements in cells but I need some help on the VBA code.




JMW

http://www.ViscoAnalyser.com

 

[jmw]

And if I have to do it all on the same sheet then I'll write this in obscure cells and hide the column or row.

JMW

http://www.ViscoAnalyser.com

 

[MintJulep]

Complete cell references, including sheet name take the form of "Sheet1!A1", or if your sheet has an actual name "MySheetName!A1"

so

Something like this:

 IF Range("C1").Value = 1 THEN range("sheet1!A1").Formula = "=b1+c1+d1"

 

[jmw]

Thanks MintJulep,
that has been a great help - so far.

I am working on a blend calculation.
I have two component streams and tabulate their properties.
I have a blend stream.

The user will either know the blend ratio and calculate the the blend properties or he may select one of two properties (viscosity or sulphur content) and enter them into the blend properties and calculate the required ratio from these values.

All the other properties in the blend properties table are calculated based on the mass or volume ratios, whether the ratios are found by calculation or entered by the user.

The sulphur and viscosity are the two properties which complicate the issue.
Either the values in these cells are calculated based on a ratio or one or other of them will be written to and used as a means to calculate the ratio.

Hence, since I need to be able to write to these unprotected cells and that means that if I want the contents to be calculated then I have to make the calculation separate and write the results into the cell overwriting whatever is already in there.
But only one of the two properties will ever be used to calculate the ratio. It means that if sulphur is chosen as the target, the viscosity will be calculated and visa versa.

So I need to use a drop down to choose whether to calculate the ratio (one property is written and the rest calculated) or the properties, in which case all are calculated (target cell C11) and another drop down to allow him to specify whether sulphur or viscosity is the target (cell C12).

This is a bit more complex.

I have gotten so far as the following:

Private Sub Worksheet_Calculate()
If Range("C11").Value = 1 And Range("c12").Value = 1 Then Range("C8") = Range("D8")
If Range("C11").Value = 1 And Range("C12").Value = 2 Then Range("C10") = Range("d10")
End Sub

In which cells C8 (sulphur, for example) an C10 (viscosity) are such that one can be written to by the user as a target value or by the formula result in a corresponding cell - D8 for sulphur, D10 for viscosity.

Thus, if the user wants to calculate the ratio (C11 =1)based on a target sulphur value (C12=1), he will write the value in the sulphur cell (C8) and Viscosity (C10) will be written to by a formula (D10) but if he wants to calculate based on viscosity (C12=2), he will write the viscosity value in C10 and D8 will calculate the sulphur and enter it into C8.

I'm not sure I have reached that point yet.



JMW

http://www.ViscoAnalyser.com

 

[MintJulep]

I suggest that you use a VBA User Form for the interaction with the user.

It will make life much easier, and give you much greater flexibility with far less opportunity for things to go wrong.

 

[jmw]

It doesn't get any easier.
I'm using Excel 2002 by the way.

I have three possible conditions which I can manage with IF statements e.g.:

Private Sub Worksheet_Calculate()
If Range("AC17").Value = 1 Then Range("J15") = Range("AD13")
If Range("AC17").Value = 2 Then Range("F7") = Range("AD8")
If Range("AC17").Value = 3 Then Range("F7") = Range("AD8")
End Sub

And this works.
BUT
I want two actions performed for each If condition, e.g. for the first If statement I also want Range("J15") = Range("AD13").
I can't tag on another line so that says:

If Range("Ac17").value = 1 Then Range("J15") = Range("AD13")
If Range("Ac17").value = 1 Then Range("J11") = Range("AD11")

Because, of course, it stops the moment it has completed the first true If statement.

So I try to use the block function
Examples Googled from various sites seem not to work.

Private Sub Worksheet_Calculate()
If Range("AC17").Value = 1 Then
Range("J15") = Range("AD13")
Range("J11") = Range("AD11")
Else: If Range("AC17").Value = 2 Then
Range("F7") = Range("AD8")
Range("J15") = Range("AD13")
Else: If Range("AC17").Value = 3 Then
Range("F7") = Range("AD8")
Range("J11") = Range("AD11")
End If
End Sub

But I keep getting syntax errors.
I want to write Else If but if I do, Excel converts it to Esle: If and then when I step through with key F8 I get the message "Else without If".
It doesn't like its own automatic correction!

So I modify it to ElseIf which it doesn't like, or Else by itself which it definitely doesn't like.

Any ideas?

JMW

http://www.ViscoAnalyser.com

 

[electricpete]

Have you looked at "select case" statement?

=====================================
(2B)+(2B)' ?

 

[electricpete]

I can't tag on another line so that says:

If Range("Ac17").value = 1 Then Range("J15") = Range("AD13")
If Range("Ac17").value = 1 Then Range("J11") = Range("AD11")

Because, of course, it stops the moment it has completed the first true If statement.

I'm not sure why it stops. It shouldn't. But you can do the same thing a little cleaner as follows;

If Range("Ac17").Value = 1 Then
    Range("J15") = Range("AD13")
    Range("J11") = Range("AD11")
End If

=====================================
(2B)+(2B)' ?

 

[electricpete]

Really I'm not sure where the problem is (maybe I'm misunderstanding). But there are a wide variety of very capable flow control structures in vba. Here is another one:

IF condition THEN
do stuff
do stuff2
ELSEIF condition THEN
morestuff
morestuff2
ELSEIF condition THEN
evenmorestuff
evenmorestuff2
ELSE
againmorestuff
againmorestuff2
ENDIF

You can put as many statements as you want in each block. None of the elseif clauses gets executed if any prior tested condition is true. The final else gets executed only if all prior tests are false.



=====================================
(2B)+(2B)' ?

 

[IDS]

If you wanted to check every If statement, no matter if the previous ones were true or false, then they are separate Ifs, and need to be terminated with an End If:

Private Sub Worksheet_Calculate()
If Range("AC17").Value = 1 Then
Range("J15") = Range("AD13")
Range("J11") = Range("AD11")
End If
If Range("AC17").Value = 2 Then
Range("F7") = Range("AD8")
Range("J15") = Range("AD13")
End If
If Range("AC17").Value = 3 Then
Range("F7") = Range("AD8")
Range("J11") = Range("AD11")
End If
End Sub

But in your case AC17 can't be equal to 2 if it is equal to 1, so to save wasted computation you want:

Private Sub Worksheet_Calculate()
If Range("AC17").Value = 1 Then
Range("J15") = Range("AD13")
Range("J11") = Range("AD11")
ElseIf Range("AC17").Value = 2 Then
Range("F7") = Range("AD8")
Range("J15") = Range("AD13")
ElseIf Range("AC17").Value = 3 Then
Range("F7") = Range("AD8")
Range("J11") = Range("AD11")
End If
End Sub

It is ElseIf, not Else: If or Else If


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[jmw]

Thanks Electric Pete and IDS.
I have tried both approaches and was considering the case function, but...

If I use your expression IDS, and step through with F8, it ignores every second function.
If I use your Elseif expression Electricpete, I get the same problem.
I just can't seem to get it to see the next line, even when I toggle through the conditions for each If statement in the spreadsheet.
e.g.
Private Sub Worksheet_Calculate()
If Range("AC17").Value = 1 Then
[highlight]Range("J15") = Range("AD13")[/highlight]
[Range("J11") = Range("AD11")
End If
If Range("AC17").Value = 2 Then
[highlight]Range("F7") = Range("AD8")[/highlight]
Range("J15") = Range("AD13")
End If
If Range("AC17").Value = 3 Then
[highlight]Range("F7") = Range("AD8")[/highlight]
Range("J11") = Range("AD11")
End If
End Sub

So F8 never highlights other than the first range function.

PS I have Worksheet selected in the first dropdown and Calculate in the right hand drop down.

I suppose this isn't some wretched limitation of Excel 2002?

Plus I have the problem that once it runs, it gets into a recalculation loop. I will need to fix that too. There are some suggestions in Google but If I can first fix this...

And thanks for the much appreciated help guys.

JMW

http://www.ViscoAnalyser.com

 

[jmw]

Thanks very much folks, your contributions have certainly helped me.
I find that in cribbing the original statement from the Internet I was picking the wrong procedure.
The code that works is the change value:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("AC17").Value = 1 Then
Range("J11") = Range("AD11")
Range("J15") = Range("AD13")
ElseIf Range("AC17").Value = 2 Then
Range("F7") = Range("AD8")
Range("J15") = Range("AD13")
ElseIf Range("AC17").Value = 3 Then
Range("F7") = Range("AD8")
Range("J11") = Range("AD11")
End If
End Sub

And no endless calculation cycles either!




JMW

http://www.ViscoAnalyser.com

 

[jmw]

Having written the VBA code, (and spent far too long sorting out pour point index calculations - on one page the expression uses Ln and on another page further on it uses Log) I have then protected the sheet and workbook.

I then found that the "view code" option on the sheet tab is not protected.
I have then had to do a google to get the answer and then access the VBA editor and protect it from there.
Why wouldn't you be able to protect this from the workbook and sheet protection menu, either as a fresh item in the menu "Protect Projects/macros" or in the tick boxes of things to protect on protect sheet or workbook options?

Did all those people who claimed "I am a PC. Windows 7 is my idea" think of this or does the problem persist in later versions of Excel?


JMW

http://www.ViscoAnalyser.com

 

[IDS]

You control the access to the workbook from the workbook menu, and access to the VBA code from the VBE menu.

Why is that a problem?

What is more of a problem is that the password to the code is easily removable by anyone who knows the procedure.

If you just want to stop accidental changes or casual tampering then making the code hidden is fine, but if you want to stop any unauthorised person from looking at the code you need a different solution.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/