Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Is Excel unreliable ?
Pressing F9 / Shift+F9 doesn't update my calculated cells reliably, i.e. it just doesn't do it or gives wrongs answers. The only way to get the right answers is to drag down the calculated cells again and then press F9 / Shift+F9. Before going into more detail I just wanted to know if I am encountering a common problem ? -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Is Excel unreliable ?
I don't understand what you mean by 'drag down the calculated cell'
Also could we see the particular formula that Excel is getting wrong. I'm not aware that this is a general problem but others may differ. Mike "DesCF" wrote: Pressing F9 / Shift+F9 doesn't update my calculated cells reliably, i.e. it just doesn't do it or gives wrongs answers. The only way to get the right answers is to drag down the calculated cells again and then press F9 / Shift+F9. Before going into more detail I just wanted to know if I am encountering a common problem ? -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Is Excel unreliable ?
Do you have your calculation set to manual? Check Tools - Options
-Calculation "DesCF" wrote: Pressing F9 / Shift+F9 doesn't update my calculated cells reliably, i.e. it just doesn't do it or gives wrongs answers. The only way to get the right answers is to drag down the calculated cells again and then press F9 / Shift+F9. Before going into more detail I just wanted to know if I am encountering a common problem ? -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Is Excel unreliable ?
Look he
http://xldynamic.com/source/xld.xlFAQ0024.html -- Kind regards, Niek Otten Microsoft MVP - Excel "DesCF" wrote in message news:op.ts8qz901upgxg0@descstar... | | Pressing F9 / Shift+F9 doesn't update my calculated cells reliably, i.e. | it just doesn't do it or gives wrongs answers. The only way to get the | right answers is to drag down the calculated cells again and then press F9 | / Shift+F9. Before going into more detail I just wanted to know if I am | encountering a common problem ? | | | | | -- | Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Is Excel unreliable ?
Here is the function I am using. It is used in sheet 2 and references cells in sheet 1. It is used twice in two adjacent cells and the results are then summed in a third adjacent cell. In a forth adjacent cell the figure in the third adjacent cell is added to another figure taken from the immediately preceding row in the forth adjacent cell. Public Function sumValues(strName As String, strType As String) As Currency Dim intI As Integer Dim intJ As Integer Dim curC As Currency Select Case strType Case "D" intJ = 7 Case "C" intJ = 8 End Select With Sheet1 For intI = 1 To 10000 If .Cells(intI, 3) = strName Then curC = curC + .Cells(intI, intJ) End If Next End With sumValues = curC End Function On Fri, 01 Jun 2007 13:47:00 +0100, Mike H wrote: I don't understand what you mean by 'drag down the calculated cell' Also could we see the particular formula that Excel is getting wrong. I'm not aware that this is a general problem but others may differ. Mike "DesCF" wrote: Pressing F9 / Shift+F9 doesn't update my calculated cells reliably, i..e. it just doesn't do it or gives wrongs answers. The only way to get the right answers is to drag down the calculated cells again and then press F9 / Shift+F9. Before going into more detail I just wanted to know if I am encountering a common problem ? -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Is Excel unreliable ?
You need to add volatile to your function
-- Regards, Peo Sjoblom "DesCF" wrote in message news:op.ttgp7tizupgxg0@descstar... Here is the function I am using. It is used in sheet 2 and references cells in sheet 1. It is used twice in two adjacent cells and the results are then summed in a third adjacent cell. In a forth adjacent cell the figure in the third adjacent cell is added to another figure taken from the immediately preceding row in the forth adjacent cell. Public Function sumValues(strName As String, strType As String) As Currency Dim intI As Integer Dim intJ As Integer Dim curC As Currency Select Case strType Case "D" intJ = 7 Case "C" intJ = 8 End Select With Sheet1 For intI = 1 To 10000 If .Cells(intI, 3) = strName Then curC = curC + .Cells(intI, intJ) End If Next End With sumValues = curC End Function On Fri, 01 Jun 2007 13:47:00 +0100, Mike H wrote: I don't understand what you mean by 'drag down the calculated cell' Also could we see the particular formula that Excel is getting wrong. I'm not aware that this is a general problem but others may differ. Mike "DesCF" wrote: Pressing F9 / Shift+F9 doesn't update my calculated cells reliably, i.e. it just doesn't do it or gives wrongs answers. The only way to get the right answers is to drag down the calculated cells again and then press F9 / Shift+F9. Before going into more detail I just wanted to know if I am encountering a common problem ? -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Is Excel unreliable ?
You haven't told VBA that sumValues depends on Sheet1!A1:A10000, so it
doesn't know to recalculate sumValues when a value in that range changes. Either add Application.Volatile at the beginning of the function, or include the range as an argument to the function. However, I'd think you could do much faster and more efficient with a built-in function: =SUMIF(Sheet1!$C$1:$C$10000, strName, IF(strType="D", Sheet1!$G$1:$G$10000, Sheet1!$H$1:$H$10000)) In article <op.ttgp7tizupgxg0@descstar, DesCF wrote: Here is the function I am using. It is used in sheet 2 and references cells in sheet 1. It is used twice in two adjacent cells and the results are then summed in a third adjacent cell. In a forth adjacent cell the figure in the third adjacent cell is added to another figure taken from the immediately preceding row in the forth adjacent cell. Public Function sumValues(strName As String, strType As String) As Currency Dim intI As Integer Dim intJ As Integer Dim curC As Currency Select Case strType Case "D" intJ = 7 Case "C" intJ = 8 End Select With Sheet1 For intI = 1 To 10000 If .Cells(intI, 3) = strName Then curC = curC + .Cells(intI, intJ) End If Next End With sumValues = curC End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Scrolling in Excel unreliable | Excel Discussion (Misc queries) |