Free Microsoft Excel 2013 Quick Reference

Excel formulas are not automatically updating

Hello All,

Happy New Year! I have an excel issue where the formulas are not updating
unless I click on the cell and then place the cursor on the cell address
field (at the top of the excel sheet). Once I do that and click away, the
cell displays the updated total. Is there an option/setting that is causing
this? The spreadsheet was working fine before. Thanks.

Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200601/1


Post your answer or comment

comments powered by Disqus
Hello All,

Happy New Year! I have an excel issue where the formulas are not updating
unless I click on the cell and then place the cursor on the cell address
field (at the top of the excel sheet). Once I do that and click away, the
cell displays the updated total. Is there an option/setting that is causing
this? The spreadsheet was working fine before. Thanks.

Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200601/1

I am having a new problem with my Excel spreadsheets that just started late
yesterday. The cells are not automatically calculating the information. I
checked the formulas and they are all pulling from the correct cells. I can
click on the formula bar and then it will calculate, but just yesterday
morning, they all calculated automatically.

This is only in Excel. The other applications are running fine. If you
have a solution to this problem, please let me know.

Thank you.

First.. the answer is yes I have Auto Calculation's on.

I am running a macro that toggles auto calc on and off. Appartenly, I have
either confused or corrupted excel.

In a nut shell after the macro has run.. my formulas to not work.. very
simple formulas like = A8, do not work. I can change the value in cell A8
and my root formula does not change. Unless I physically go into the cell
that the formula is and manuall hit enter from the fromula window.

The only global fix I have found is to save the filename as a differnt file.
This seems to prompt excel to register the formulas and they update.
However, this only works for that one instance. What I mean is If I simply
re-open the file, without doing anything else.. the formulas are not active.
I..e save and 1 time the formulas update.. if I open and type in a new result
in cell A8.. the formula does not change?

Please help? This is part of a huge project I have been working on at work
and my deliverable is this week!!!!!

My Excel calculations are not automatically updating - & I'm in automatic calculation mode. Does anyone know what might be causing this?

I work with big excel files (almost 7MB), with most of the worksheets linking
back and forth. I have discovered that sometimes when I change a cell on a
worksheet, it does not automatically update the cells on the linked
worksheets. The only way to do it is to go to each dependent cell
individually to update, this is very time consuming. I have double checked
that the automatical update is checked in the options. Has anyone
experienced this and does anyone have a solution? Thanks.

Hello,

The below is my problem. I have a excel wokbook that contains 2 reports from an outside source and on top of the reports i have some excel calculations.One of report has drill down capability. Everytime i open my excel workbook and refresh it the excel forumals also get refreshed along with my 2 reports and i get my results. But when ever i drill down something on the report the excel formulas are not getting refreshed automatically. I end up refreshing the whole workbook to get the correct results which is a very time consuming process considering the amount of data i am trying to pull. And drilldown is something the users do very often and they don't like refreshing the whole workbook.

Is there any way to update the excel functions automatically whenever i drilldown?

Please help.

I have an excel 2000 file that I use for work. I have formulas thorughout
the workbooks, that read off of the first workbook. I also have several
simple formulas, calculating for example 10% of an an amount. However as I
am typing in the information necessary for the formulas to work, the formulas
are not updating. One example: I have letters throughout my spreadsheet
(actual letters that go out to people). I type in information in the first
workbook and they are supposed to carry over to the letters so all I have to
worry about is the body of the letter. Yet it is not carrying over. If any
one can help me figure out what is going on that would be awesome. I can go
in click in the formulas and the formula will work, but as soon as I go in a
start changin the actual information, the formulas will not update either.

Thanks

Hi,

After reading various ideas etc on here i have setup several connections from a spreadsheet to different .csv files always stored in the same place on the I:/ drive - a usb stick.

first time i set up the spreadsheet the external links automatically updated upon pressing the refresh button.

since then however when i choose refresh Excel wants me to confirm the file for some reason (showing the explorer etc and i have to click the file). the trouble is as i have several files i dont know which one Excel is asking me for so i have a danger of pointing the wrong data to each link.

any ideas why these text files are not automatically updating as the files dont change name or move but do get updated?

Is there a simple way to make a SUM formula expand to include a new row if
the row is added as the first or last row of the SUM range?

For years we have been frustrated that rows added to the extremes of a range
included in a SUM formula are not automatically included in the range used
in the formula. This is also true of many other functions.

It seems logical to us, that if we select any row within a range, and insert
a new row, the new row should be included in any formula addressing the
original range. On the other hand, if we select a row contingent to, but
outside a range, and add a new row, it is logical that the new row not be
included in the formula addressing the original range. To us this seems a
logical approach and gives the user a predictable way to quickly add rows
(or columns) and know if any forumula referencing the range will, or will
not, be automatically included in the range.

Nevertheless, the user currently has a problem if he/she adds a new row
above the first row or beneath the last row of a range formula range. Such
new rows are not automatically included in the original range reference used
by a formula. This is especially problematic if the user originally defines
a range of one row since a later addition to the range will never be
automatically included in the SUM formula addressing the original range.

Please note that the same problem we have with rows also applies to columns.

As a work-around we have used the OFFSET formula to address a larger range
than the actual SUM range. This works very well, but generates huge
overhead in developing the original formulas or worksheet. We have also
tried adding a phantom row of minimal height (or hidden), between the last
row of a range and the sum formula in a 'Totals' row. This allows the user
to add new rows at the end of the SUM range. But this two generates
significant overhead and complicates the worksheet.

Does anybody know of a simpler approach, or of any configuration change,
that would force new rows to be included in the original range? Thank you
for any suggestions.

Richard

Help! Excel 2007 and Excel 2003 are not compatible?

Hi all,

I just don't understand this.

I have a Excel sheet which was created in Excel Xp 2003. And it is
full of calculations.

When I open it in Excel 2007,

And press SHIFT+F9, it always gives "NO-CELL" error.

Basically it doesn't run at all.

But when I go to each cell, and following the logical dependency, and
refresh/recalculate each cell one by one,

then the results are correct...

But there are so many cells and manually going through them are
infeasbile.

What can I do now?

Can anybody help me?

Thanks!

Hi All,

I got a Excel workbook from a friend. I observed that in a worksheet the excel formulas are not working i.e. if i type a formula in a cell and click enter the cell displays the formula but not data for the formula.

E.g.: In attached screen shot, the columan P should display Date instead =A1 is displayed.

Please let me know how to enable the formulas.

Thanks & Regards,
Abhi Ram

I have a sheet with a cell that is an HLOOKUP, however this does not automatically update whenever you open the file, and instead you need to highlight over the cell and hit the ENTER key and then it will update.

Any help, thx

=HLOOKUP(D$2,'Network_Address[Last_Name.xls]Sheet1'!$E$3:$BD$23,21,FALSE)

I am trying to create or find an excel formula that would automatically turn column G (Days til standard is met) red once it is within 10 days of column J (Pending Stage State Standard) .

I would need column J to change colors or just show the number automatically.

Hi!

I wonder has anyone had the same problem as I do now?!

I have a file with lot's of formulas where I have recently added macros. For some reason the formulas in that sheet (f.e. offset, index&match etc) are not working automatically anymore. Instead, if I go to the cell contained formula and press F2 and exit the cell, only then the cell with the formula will be updated. Is it because of macros???

If someone knows the solution to this, I be very glad to have opinions on this! Thank you in advance!

My Excel formulae used to always update immediately, and now they don't. I
have a formula: =(K10-F10)*E10. Now when I insert new values for K10, F10
or E10, the result does not update until I click on the results cell and make
to edit it, and press return. Can anyone explain why I have lost the
automatic updating of formulae? TIA.

I have Excel 2003, and get automatic Office updates. Lately, I've noticed
that some linked cells do not automatically update when I change the value or
formula in the original cell. I have to click on the linked cell and hit
enter before the cell will update with the changed value. There seems to be
no pattern for which linked cells update and which do not (for example, the
original cell may have a percentage, a constant value, or a formula). Some
of my workbooks have fifty or more worksheets, so this is becoming a real
problem. Help!

I am working with a sheet that has a subtotal at the bottom of a column. When I want to add data, there is a macro that inserts a new row, but when I add the data, the cell that shows the total for the column does not update to include the new row.

For example, the formula in the subtotal column is this:

=SUBTOTAL(9,G7:G26)

When I insert a row, if the sheet is unprotected, the formula will update to show:

=SUBTOTAL(9,G7:G27)

When I insert a row, if the sheet is protected, the formula does not update.

If I unprotect the sheet, the formulas are likely to be tampered with, and if I only unlock that one cell, that one may be altered as well. Can I do anything that would allow the sheet to remain protected, but update the formula when new data is entered?

Also, any other suggestions for the layout or design of this sheet would be great. I'm still learning a lot about excel. Thanks for any help.

--One more thing, I did add validation to the cell as suggested in another thread, and left the cell unlocked, but discovered that if I hit the "Delete" key when the cell is selected, it clears the formula.

Hello.

I did a search for this question, and came up with a post, however the response did not make sense to me, so I am posting my question new (rather than replying to the other, since it was from 1+ years ago).

I know next to nothing about VBA (yet). I found the code I am using from this website (and altered it a bit), and it's purpose is to sum the cells that have a particular font color format. It is:


	VB:
	
 Range) 
     
     ''''''''''''''''''''''''''''''''''''''
     'Written by Ozgrid Business Applications
     'www.ozgrid.com
     
     'Sums cells based on a specified fill color.
     '''''''''''''''''''''''''''''''''''''''
    Dim rCell As Range 
    Dim iCol As Integer 
    Dim vResult 
     
    iCol = rColor.Interior.ColorIndex 
     
    For Each rCell In rSumRange 
        If rCell.Font.ColorIndex = iCol Then 
            vResult = WorksheetFunction.Sum(rCell) + vResult 
        End If 
    Next rCell 
     
    SumColor = vResult 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When cells within the range are given a particular color, the function does not automatically update; it requires me to double click on the cell w/ the function. The only time it does auto update is when I subtract a cell with a particular color.

How can I automate this function, just like all the others that are built into Excel act?

Here is the forum post from last year with the answer I do not quiet understand.

http://www.ozgrid.com/forum/showthre...pdate+function

...how do I incorporate 'Application.Volatile' in the above code?

Thank you for your time & help...

Help... I had been playing around with trying to get a user form to work (unsuccessfully) before I finnaly just gave up, but now when I open all any of my excell sheets my formulas do not updated when a value is changed in another cell that should change the formula outcome. The only way the value changes is if I go to the cell that has the formula in it and go up to the location bar and put the cursor at the end of the formula and press enter forcing it to re-evaluate the formula.

I provide an Excel spreadsheet to my field sales team for quoting projects.
It has worked very well for the past few years, but in the last revision I'm
getting calls from sales reps saying that some formulas are not calculating.
It's not as if an error is displayed - the formula simply does not calculate.
Also, it is the same group of formulas each time.

However, when I test the file out on my computer, all formulas calculate. I
cannot recreate the problem on my computer.

Is there a setting in Excel that limits the number of possible calculations?
Any insight that can be provided on why this is happening would be greatly
appreciated. I am truly at my wits end.

I have an excel 2007 file with worksheets for each month. The Feb-Dec sheets
each have cells that contain the formula "=B25+PrevSheet(F25)" to accumulate
the sum of corresponding cells of earlier month worksheets using a function
"PrevSheet(x)" that I wrote to pick up the specified cell value from the
previous month worksheet. Each worksheet has a fairly large number of cells
that use this formula.

When I make changes in earlier month sheets the following month sheets do
not automatically update their cumulative values. Clicking Recalculate does
not perform and update either. The only way I seem to be able to update
these cum cells is to select each one, click up in the formula bar and then
press enter. This is a very tedious process.

How can I get the cells to update automatically?

Thank you for your help, John

Formula covers columns G thru L = formula in column M Ex. =Sum(G1:L1)
Insert columns M thur S -- formula now in column T Ex. should read
=Sum(G1:S1)
Column T Formula will not automatically update to include columns M thru S

Version Excel 2003

--------------------------------------------------------------------------------

I am using VLOOKUP to pull data from one sheet to another.

When I insert a column in the sheet referenced in the VLOOKUP formula the formula is not auto updating. Is there a preference setting that I have erroneously changed?

i.e. =VLOOKUP(A3,'Sheet1'!$T:$X,3,FALSE) and the 3 should change to a 4 if I insert a column in column 1 through 3 in Sheet 1.

I have numerous spreadsheets that need to be printed showing the formulas.
However, some of the formulas are derived using a link to a different
spreadsheet (not created on our network). When we open the sheet and choose
to not update the links, the spreadhseet opens, and when we view the formulas
they appear. However, the file path for some of the formulas also appears
using my network information (e.g. c:documents and settingsname, etc....).
Is there a way to retain the original path information? Thank you.


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