Free Microsoft Excel 2013 Quick Reference

Functions not re calculating automatically

i've lost all my automatic re-calculation ability in ALL of my spreadsheets
if i change a number it will not re calculate automatically - i have to go
to the function line, put my cursor at the end of the formula and hit enter
for it to re-calculate. What have I done?
I'm using Excel 2002.
Thank you


Post your answer or comment

comments powered by Disqus
I have not created many user-defined functions, so I may be doing something wrong here. But, I have created one which works as required, but it does not re-calculate automatically. If I edit the cell in which it is used, then it recalculates.

Any ideas? (My options are set for Automatic Calculation BTW)

DuckBill

I have created a function (shown below) and I call it from my excel sheet with the following command

=Progress_Status(Row())

Calculate is set to Automatic. The cells type is General - Not Text (I read this may be the issue, but it is not)

It calculates fine if I select the cell and press enter to re-enter the formula. But if I change any of the cells that the formuale uses it does not do anything, until I re-selct and re-press enter. I have tried doing it in various ways but no joy.

The function is as follows:-
Function Progress_Status(ByVal ThisRow As Integer) As String

Dim Status As String
    Status = "On Target"
    If Cells(ThisRow, 13) = "Yes" Then
        Status = "Completed"
    ElseIf Cells(ThisRow, 14) = "On Hold" Then
        Status = "On Hold"
    ElseIf Cells(ThisRow, 13) = "No" And DateDiff("d", Cells(ThisRow, 12), Now) > 0 Then
        Status = "Delayed"
    ElseIf Cells(ThisRow, 11) = "No" And DateDiff("d", Cells(ThisRow, 10), Now) > 0 Then
        Status = "Delayed"
    ElseIf Cells(ThisRow, 9) = "No" And DateDiff("d", Cells(ThisRow, 8), Now) > 0 Then
        Status = "Delayed"
    ElseIf Cells(ThisRow, 7) = "No" And DateDiff("d", Cells(ThisRow, 6), Now) > 0 Then
        Status = "Delayed"
    End If
           
    Progress_Status = Status

End Function
Any help would be as always much appreciated

Hi,
I have created a function which is a webquery. I am using this function a little bit everywhere in my workbook.

Now everytime i do a change in my workbook - this function get called up. The problem is that as I call the function a lot of time - Excel gets hanged-up for little bit which annoying.

What I would like is to use a toggle bottom (in a toolbar for example - I already did that part) when true the function are updated with the rest of the workbook, when false the function are not calculated and the cell with the function keep the same value. As a requirement i want all workbook to remain in automatic calculation.

Thank you very much for your proposition.

As a note I have tried the following without satisfactory solution:
#Failed1 - Select which sheets should .calculated and which sheet should not (the ones with the function) but since i am using the function in all sheets, i would have to set the workbook calculation to manual and that is not satisfactory.
#Failed2 - I could use a boolean flag that I would toggled when i want the function to be used while i modify the sheet. For that I am putting a "if tag = true" loop in the function code. However the problem is that when flag = false - the function gives !NAME in the cell.

Excel 2003 does not calculate automation add-in functions automatically when
a spreadsheet is opened. However, xla functions are still calculated
automatically when a spreadsheet is opened.


I have a worksheet where different cells need to be calculated each week. I've done that using an IF function.

But, once the week changes I need the same cells to NOT re-calculate. And different cells to calculate based on the new date. Once we move to the week of 8/20 I need cell C1, C2, and C3 to hold the value of week of 8/13. Right now, I'm using a circular reference but don't want to get the error message or have to adjust the iterations option.

Is there a way to do this without being a code-writing wiz kid?

i.e.
cell B1 contains the current week's control date (will always be a Monday)
cell A2 contains each week's date (will always be a Monday)
cell C2 contains =IF($A2=$B$1,100,C2)

Hi all,

Unsure why this problem is happening, but i am currently developing an excel model (quite large in size) [Excel 2007, but also occurs in Excel 2003].

The issue I run into is that in certain vba macros i have been writing (and i suspect it is also now occuring in other macros which were previously working), is that the "calculate" line in the coding does not re-calculate certain cells (in regards to a loop function, which keeps looping until a certain cell becomes negative in value)

If I open the vba editor and step thru the macro line-by-line, it calculates the values correctly when instructed if i pause after the "calculate" line, and keeps looping till the cell being tested becomes negative.
But if i hold down F8 (or let it run automatically via F5) it runs thru the macro quickly and updates cells which determing the scenario, but then the cell to be tested for to determine when the loop ends does not update, and hence it keeps looping and goes past the point where it should stop.

Any ideas on why this may be happening and how to solve it would be highly appreciated, especially as i suspect its worringly also happening in other macros within the model too now.

Any help appreciated.. Thanks!

Hi,

I have a spreadsheet which plots a chart based on some input data and a number of calulations (functions) in a module. There is a lot of data and when you change an input value, the spread sheet takes a minute or so to re-do the the calculations and then re-plot the new chart. Could any one tell me how to do the following:

1. Stop excel from automatically re-calculating everything after each input change i.e. disable the automatic re-calculations so the user can make multiple input changes without waiting for re-calculations after each new input.

2. Add a button that allows you to perform the re-calculation once you have made multiple input changes.

Any help would be greatly appreciated.

Best Regards,

Aaron

I am an advanced excel user and this is the first time i have encountered this problem.

My xls contains a mixture of arrays, lookups and sumif functions.

MY array is pulling through the updated information (eg. a sum of spend on a category), and this in turn feeds to a total by quarter. HOwever the simple formula which adds the three months of the quarter is not updating.

However, if i go to the cell with the forumla (which is as simple as a1+a2+a3) press F2 to edit, then press enter, hey presto it calculates?

Weird.

any ideas?
I dont want to go into each cell and do that as there are thousands!!

My company has recently deployed MS Office 2003. The previously deployed version was Office 2000.

One of our users has an 11MB financial workbook that contains lots of formula. Mostly of IF, SUMIF and SUM calculations. There are plenty of links between the 25 worksheets within the one workbook but no links to other workbooks.

Entering data into the workbook using Excel 2000 is nearly instantaneous. However entering data into Excel 2003 takes about 15 seconds each time I press enter (or direction key) as the workbook recalculates each time.
The settings for calculation are identical with calculation = automatic.

Any idea how I can achieve the same performance in 2003 that 2000 delivers?
The calculation algorithm in Excel 2002 and 2003 is supposed to better than earlier versions.
I do not want to go to manual calculation as this is seen as a negative step by the user community seeing that they did not need to do that in Excel 2000.

I have confirmed that there are no user defined functions either in this workbook or in my personal.xls file. This workbook also has no macros.

I have an issue where whenever the cells on a worksheet are selected for
re-calculation, it gets "stuck" at 0% and causes Excel to stop responding.

The spreadsheet is designed to do the following:
- First worksheet lifts data from another spreadsheet via link function.
- Second worksheet calculates totals based on the data in the first
worksheet. For example, calculations include things like:
=SUMIF('First Worksheet'!D:D,"Michael",'First Worksheet'!I3:I65535)
This is done to sum all of Michael's entries and is repeated for each person.
There is then a total to sum entries for groups of people.

I have tried re-calculating based on auto and manual with the same results.
The CPU appears to max out at 100% and the application hangs.

I am running WinXP SP2, Excel 2003, P4 2.4Hz, 512MB RAM and have downloaded
all Excel 2003 updates.

Just as a sidenote, if I re-create the formula in a new cell it calculates
instantly. Is it possible that the formulas need to calculate in a certain
order and this is causing the problem?

I would appreciate any help you can offer with this problem.

Marina

Hi all,

I'm running a macro with under a

	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
-state. Part of my macro is the

	VB:
	
.Replace 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
statement. Is there a way to
prevent a User Defined Function (UDF) from re-calculating while running
this

	VB:
	
.Replace 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
-statement? This replacement doesn't affect the UDF
values and calculating takes a long time.

FYI, this UDF doesn't have the

	VB:
	
Application.Volatile 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
-statement.

Thanks in advance for any feedback!

- Bas

Hi all -

Trying to create a new book
Copy from book.data to newbook.data

Would like to append current date to newbook
I am receiving "Sub or Function Not Defined error" here :


	VB:
	
 wbnewBook = Workbooks.Add 
wbnewBook.SaveAs "C:DeleteAR_CreditCard" _ 
& Format(Today(), "mmddyyyy") 
With wbnewBook.Sheets(1).Name = "Data" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Specifically on Today()

Thanks much
-marc

Full :

	VB:
	
 ar_CreditCardDumpProcess() 
     '//Declarations
    Dim wbBook As Workbook 
    Dim wsData As Worksheet 
    Dim rng As Range 
    Dim lngrows As Long 
    Dim rngCopyFrom As Range 
    Dim wbnewBook As Workbook 
     
     '//Environment
    Application.ScreenUpdating = True 
    Application.Calculation = xlCalculationAutomatic 
    Application.DisplayAlerts = True 
     
     '//Objects
    Set wbBook = ThisWorkbook 
    Set wsData = wbBook.Worksheets("Data") 
     
     '//Processes
    With wsData 
        lngrows = .Range("A65536").End(xlUp).Row 
    End With 
     
    With wsData 
        Set rng = .Range("A1:K" & lngrows) 
    End With 
     
    With rng 
        .AutoFilter Field:=11, Criteria1:="Credit Card" 
    End With 
     
    With wsData 
        Set rngCopyFrom = .Range("A1:K" & lngrows).SpecialCells(xlCellTypeVisible) 
    End With 
     
    Set wbnewBook = Workbooks.Add 
    wbnewBook.SaveAs "C:DeleteAR_CreditCard" _ 
    & Format(Today(), "mmddyyyy") 
    With wbnewBook.Sheets(1).Name = "Data" 
         
        rngCopyFrom.Copy _ 
        wbnewBook.Worksheets("Data").Range("A1") 
         
        With rng 
            .AutoFilter 
        End With 
         
        wbnewBook.Save 
        wbnewBook.Close 
         
         '//Cleanup
        Set wbBook = Nothing 
        Set wbnewBook = Nothing 
        Set wsData = Nothing 
        Set rng = Nothing 
        Set rngCopyFrom = Nothing 
         
        Application.ScreenUpdating = True 
        Application.Calculation = xlCalculationAutomatic 
        Application.DisplayAlerts = True 
         
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


XL2000: User-Defined Functions Are Unexpectedly Calculated More Than ...

XL2000: User-Defined Functions Are Unexpectedly Calculated More Than Once ... To work around this issue, do not use the Function Wizard to insert the custom function

My problem is that Excel is not updating calculations automatically. I HAVE automatic calculation switched on.

I'm using Excel 2003 and a large file that contains budgets for 30 or so projects. I run a macro to consolidate the file using arrays. Once the macros have finished some formulas are used to make a summary table on another worksheet.

Yesterday I traced a similar problem to some cells where I had pasted the correct contents into cells that have Data Validation but conditional formating would not work until I used the pulldown control to select the same value as what I had pasted.

Any suggestions would be greatly appreciated.

Probably an easy one!
When my file re-calculates the message bar at the bottom has started to
count tables up to about 50.
It seems to be slower than usual (file is 3 meg) and I would like to avoid
using manual re-calculation if possible.
Can someone explain what has determined this?
Many thanks,
Thomo

Hi,

I am having problems with a spreadsheet where the formulas are not
re-calculating, even though auto calc is on.

If i hit F2 on the formula and then enter it works though.

Any ideas?

Garry

--
Gazzr
------------------------------------------------------------------------
Gazzr's Profile: http://www.excelforum.com/member.php...o&userid=31075
View this thread: http://www.excelforum.com/showthread...hreadid=520836

Hi,
I had posted this question in the General section but thought that it may be
more appropriate here ...

I have a number of financial calculations that need to be repeated for a
list of dates where the result is dependent on the input date. I need the
results form all these dates for other calculations in the spreadsheet.

I have set the calculation to manual since the spread sheet is quite large
and use F9 to recalculate.

I used the Data - Table function to repeat the calculations for the set of
dates. However, the Table does not recalculate when I hit F9. The bottom
left portion of the Excel window says "TABLE" when I hit F9, which I guess
means that the Table has not re-calculated. I have to go the the sheet that
contains the Table and hit Shift-F9 to force the Table to recalculate.

How can I force the Table to recalculate when I hit F9, or is there a better
way to perform a repeating loop in Excel?

Thanks

Anand

Hello, I'm receiving the following error, in the code listed below (I input
asterisks at the error point):

ERROR:
Compile Error: Sub or Function Not Defined

Private Sub CommandButton1_Click()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long

**************error here **********

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100

For Lrow = EndRow To StartRow Step -1

If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell

ElseIf .Cells(Lrow, "A").Value = "APPLE" Then .Rows(Lrow).Delete
'This will delete each row with the Value "ron" in Column A,
case sensitive.

End If
Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

Can anyone tell me how to correct the situation?

Any and All Help Is Appreciated - Thank You

I use SUM(Column_Title) a lot as I often do not know how many rows the
worksheet will grow to. It works great but when I add in rows, and
double-click on the formula cell, the extra rows I added are not included in
the formula range (the colour-highlighted box).

My workaround is to double-click on the formula, then double-click the
colunm title (in Row 1). Then when I double-click the formula again, it
shows it formula range includes the rows I added.

This has been happening for a while, and the workaround is tiresome as there
are many columns in some of my worksheets (and I have to double-check to make
sure I didn't miss any). I have tried F9 and CalculateSheet, both have no
effect. Is there some way to get it to update automatically? (I thought
that was the point of it, rather than having to input a cell range.)

Excel 2000 in XP.
cheers,

My company has recently deployed MS Office 2003. The previously deployed
version was Office 2000.

One of our users has an 11MB financial workbook that contains lots of
formula. Mostly of IF, SUMIF and SUM calculations. There are plenty of
links between the 25 worksheets within the one workbook but no links to
other workbooks.

Entering data into the workbook using Excel 2000 is nearly
instantaneous. However entering data into Excel 2003 takes about 15
seconds each time I press enter (or direction key) as the workbook
recalculates each time.
The settings for calculation are identical with calculation =
automatic.

Any idea how I can achieve the same performance in 2003 that 2000
delivers?
The calculation algorithm in Excel 2002 and 2003 is supposed to better
than earlier versions.
I do not want to go to manual calculation as this is seen as a negative
step by the user community seeing that they did not need to do that in
Excel 2000.

--
tonymitchell
------------------------------------------------------------------------
tonymitchell's Profile: http://www.excelforum.com/member.php...o&userid=25812
View this thread: http://www.excelforum.com/showthread...hreadid=392134

Hello,

A co-worker has asked this question while reading an Excel Book on Formulas:

1. Starts with an empty Work-Sheet.
2. Selects Cell A1
3. Selects Insert>Name>Define.
4. Enters ToRight in the Names in workbook field.
5. Deletes the contents of the Refers to field, and types
=Sheet1!B1.
5. Click OK.
6. Type something into Cell B1.
7. Enter this formula into cell A1: =ToRight.

The formula in A1 returns the contents of cell B1.
Change the value in cell B1, and the Cell A1 changes also.

The Excel book then says that if you want to use the Formula on
a different WorkSheet, go back and change the Formula to =!B1.

The Formula now works on any Sheet, but it does not Re-Calculate
when the value of Cell B1 changes. . .and that is the question ?

Get the same issue in Excel 2000 & 2003, and automatic-calc is on, and
am using general Format of the cells involved.

The only way to get a Re-Calculate is to edit the Cell with the Formula,
and Press Enter.

Can anybody explain why the =!B1 does not Re-Calculate, but the=Sheet1!B1
does Re-calculate ? These steps are from a leading Excel Formula Book.

Thanks,

Sherry

Hi All,

I have the following UDF to check if a file exists:-

Function FileExists(fname) As Boolean

If Dir(fname) <> "" Then _

FileExists = True _

Else FileExists = False

End Function

The problem is that it doesn't re-calculate when you press F9. You have to
go into the cell by pressing F2 in order for it to re-calculate.

Adding "Applicaiton.Volatile" doesn't seem to work either.

How do you work around this?

Ta

Andi

With worksheet set to manual calculation worksheet will not re-calculate sometimes after changing or inputting data. If I save the worksheet then hit F9 it will re-calculate. Sometimes I have to exit out of the worksheet and call it back up to get it to calculate.

Dell Laptop with 2 Gig of memory should be plenty for worksheets that average in size 20Meg

This problems is driving me crazy.

Hi,
I had posted this question in the General section but thought that it may be
more appropriate here ...

I have a number of financial calculations that need to be repeated for a
list of dates where the result is dependent on the input date. I need the
results form all these dates for other calculations in the spreadsheet.

I have set the calculation to manual since the spread sheet is quite large
and use F9 to recalculate.

I used the Data - Table function to repeat the calculations for the set of
dates. However, the Table does not recalculate when I hit F9. The bottom
left portion of the Excel window says "TABLE" when I hit F9, which I guess
means that the Table has not re-calculated. I have to go the the sheet that
contains the Table and hit Shift-F9 to force the Table to recalculate.

How can I force the Table to recalculate when I hit F9, or is there a better
way to perform a repeating loop in Excel?

Thanks

Anand


No luck finding an answer? You could always try Google.