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

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

- User defined function won't recalculate
- My Function is not Auto Calculating
- Stop Custom Function Constantly Re-Calculating
- Excel 2003 does not calculate automation add-in functions automati
- IF function - do not calculate
- VBA Calculate not working! Any advice?
- Disable Automatic Re-calculations and Adding a Button to do the Job
- Cells Not re-calculating despite autocalc on
- Excel Re-calculation 2000 compared to 2003
- Re-calculation causes Excel to not respond
- Prevent Re-Calculation Of UDF While Running Procedure
- Append date to file name function not defined
- XL2000: User-Defined Functions Are Unexpectedly Calculated More Than ...
- Formulas not updating to Show Results - Automatic Calc is on
- Automatic re-calculation
- Spreadsheet not calculating
- Looping through a set of calculations
- Help With - Compile Error: Sub or Function Not Defined
- Inserted Rows not re-calculating
- Excel Re-calculation 2000 compared to 2003
- Named Formula does not Recalculate ?
- Get UDF to Re-calculate without entering cell
- Worksheet will not calculate
- Looping through a set of calculations

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

DuckBill

=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 FunctionAny help would be as always much appreciated

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.

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)

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!

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

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!!

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.

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

I'm running a macro with under a

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

VB:statement. Is there a way to.ReplaceIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

prevent a User Defined Function (UDF) from re-calculating while running

this

VB:-statement? This replacement doesn't affect the UDF.ReplaceIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

values and calculating takes a long time.

FYI, this UDF doesn't have the

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

Thanks in advance for any feedback!

- Bas

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:Specifically on Today()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

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 SubIf 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 Once ... To work around this issue, do not use the Function Wizard to insert the custom function

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.

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

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

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

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

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,

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

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

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

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

This problems is driving me crazy.

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