Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

cell with formula does not update automatically

In my excel sheet cells with formula do not update automatically. I have to refresh them for it to work.
For example:
Cell A1 I enter 5
Cell A2 I enter 4
Cell A3 I enter 'A1 + A2' it deisplays 9
Now I change A2 to 5, but A3 still displays 9 and not 10.

I need to go to cell A3 and refresh it for it to display 10.
I use Excel 2007. Please let me know what the problem is.
Thanks


Post your answer or comment

comments powered by Disqus
hi,

I have a simple formula, located in cell D7, as follows: =(B7-C7)
Data in C7 changes, but when you enter in new data the formula in D7 does not update automatically. the only way to update it is to click on the formula bar and then click on the check mark to enter. there's over 100 rows with this formula, and I don't want to update each one individually. I haven't been able to find anything in the board, and this is driving me crazy! Can anyone help? I'm working on Excel 2003.

thanks,

Deb

Hi Everyone,

I have a problem with the updating the data of my excel sheet. In case I press F9 to update, certain formulas of cells get deleted. The first column is defined as:

1. = Left(E28;5)
2. = SRCDBSUM(FY$12;$I$18&$I$19&" and Prod='"&$E87&"'")*FY$16*FY$17

Both formulas refer to products which are defined in column E. The strange thing about it is, that as long as the formula refers to the major product desciption it does not get deleted. Only if it refers the products listed, they disappear.

Thanks for helping,

Franziska

Hello all.

Here is my problem: I have a worksheet with many different formulas in many different cells. When I insert or delete a row, there is one formula in one cell that does update to reflect the change in rows. The cell is located several rows below the section where rows are changing. It is a simple formula too. Here is the formula: "=SUM($E$3:E11)*0.09". If I delete more rows that cause the highest row number to be less than 11, this formula updates. If I insert more rows and go beyond row 11, the formula does not update. I have tried using $E$11, $E11, and E$11 to no avail.

Any help is greatly appreciated. Thanks.

Hi,
I need to filter cells with formulas and not to select them , is that possible by a simple or advanced filter ?
Thank you .

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!

Question: Is there a way to make Excel automatically update these links
to the other sheets of the same workbook? The links will update IF I
insert a row or a column in the SAME worksheet but will not update if I
add the row or column in a different worksheet that is referenced by
that sheet.

I and my colleges are building a set of worksheets using EXCEL 2003
that will keep track of employee productivity. It would seem that this
is more involved that first expected, however we are getting around a
few of the intricacies and quarks that we have come across. We have set
up the Excel workbook to have a total of 32 sheets labeled Total Average
and 1 – 31 (consecutive days) each having a listing of our
employees. Each sheet allows us to enter numerical data (ex. 1, 2, or
3) for their productivity, which is then averaged using {
=IF(ISERR(AVERAGE(C3:I3)),"!",AVERAGE(C3:I3)) }, placing an
exclamation point in the cell to keep it from being blank (Assistance
by RagDyer on 'www.excelforum.com' (http://www.excelforum.com) for the
formula). This works like a dream; however we are now running in to a
new issue. This being that when we add an employee to the worksheet
(Example: Day 22) then sort the page to place the person in the sheet
in alphabetical order, the worksheet “Total Average” does
not update the links for the other employees. For better clarification:
I have Brian in cell A2, Elaina in cell A3, Joseph in cell A4, and Mike
in cell A5 with columns labeled across row 1 as A=Name, B=Day1, C=Day2,
D=Day3, E=Day4, ECT. Each cell/row has a link to an adjacent cell/row in
their corresponding day tab (Example: A2 = Brian B2 = Day 1 Average
Formula on sheet 2 cell J2, C2 = Day 2 Average Formula on sheet 3 cell
J2, ECT.). Then when you go to the sheet for the corresponding day such
as sheet 2 for Day 1, I have Brian in cell A1, Elaina in cell A2, Joseph
in cell A3, and Mike in cell A4 with my average formula in cell J1:4. I
put Jeff in cell A5 along with the averaging formula and then select
all cells from A1 to J5 and sort by Row A. This then of course places
Jeff and all the rest of the information in his row up to row A3 and
then moves Joseph and Mike down to A4 and A5. Then when you click on
[Total Average] Tab you of course will need to do the same thing to
update this sheet also. However before adding the new employee to the
list of employees I check the cell links to see if they changed to show
the new placement of the employee and they have not. They still
reference the old cells where the Employee’s average was.

--
blausen

------------------------------------------------------------------------
blausen's Profile: http://www.excelforum.com/member.php...o&userid=31785
View this thread: http://www.excelforum.com/showthread...hreadid=516059

Hi,

I have XP Pro, Excel 2002 SP3

I have a simple multi worksheet spreadsheet for my finances which do mothing more complicated than sum up some columns and fields, sometimes across worksheets within the single excel spreadsheet. There are no external references.

I also Have Microsoft ActiveSync to sync with my iPaq pda.

Everything worked perfectly for over a year, the Excel document sync'd and I could always update it.

Recently I updated Office via the microsoft site and also installed the latest version of ActiveSync (4.5)

Now 75% of the time when I modify the excel document the formulas do not update, or the cell goes blank. I've tried all the force formula update buttons and nothing has any effect. This happens with the iPaq connected or disconnected. The changes do save and eventually, after a few more times opening or if I sync and open the file on the pda, they do update themselves.

The other 25% of the time the formulas update immediately (again this happens with the iPaq connected or disconnected)

I've not been able to work out why this does it but it is very frustrating! I have uninstalled ActiveSync and reinstalled the older version and I still get the same issue.

Any ideas ... please!

Robert

Question: Is there a way to make Excel automatically update these links to the other sheets of the same workbook? The links will update IF I insert a row or a column in the SAME worksheet but will not update if I add the row or column in a different worksheet that is referenced by that sheet.

I and my colleges are building a set of worksheets using EXCEL 2003 that will keep track of employee productivity. It would seem that this is more involved that first expected, however we are getting around a few of the intricacies and quarks that we have come across. We have set up the Excel workbook to have a total of 32 sheets labeled Total Average and 1 – 31 (consecutive days) each having a listing of our employees. Each sheet allows us to enter numerical data (ex. 1, 2, or 3) for their productivity, which is then averaged using { =IF(ISERR(AVERAGE(C3:I3)),"!",AVERAGE(C3:I3)) }, placing an exclamation point in the cell to keep it from being blank (Assistance by RagDyer on www.excelforum.com for the formula). This works like a dream; however we are now running in to a new issue. This being that when we add an employee to the worksheet (Example: Day 22) then sort the page to place the person in the sheet in alphabetical order, the worksheet “Total Average” does not update the links for the other employees. For better clarification: I have Brian in cell A2, Elaina in cell A3, Joseph in cell A4, and Mike in cell A5 with columns labeled across row 1 as A=Name, B=Day1, C=Day2, D=Day3, E=Day4, ECT. Each cell/row has a link to an adjacent cell/row in their corresponding day tab (Example: A2 = Brian B2 = Day 1 Average Formula on sheet 2 cell J2, C2 = Day 2 Average Formula on sheet 3 cell J2, ECT.). Then when you go to the sheet for the corresponding day such as sheet 2 for Day 1, I have Brian in cell A1, Elaina in cell A2, Joseph in cell A3, and Mike in cell A4 with my average formula in cell J1:4. I put Jeff in cell A5 along with the averaging formula and then select all cells from A1 to J5 and sort by Row A. This then of course places Jeff and all the rest of the information in his row up to row A3 and then moves Joseph and Mike down to A4 and A5. Then when you click on [Total Average] Tab you of course will need to do the same thing to update this sheet also. However before adding the new employee to the list of employees I check the cell links to see if they changed to show the new placement of the employee and they have not. They still reference the old cells where the Employee’s average was.

I have inherited a very complex spreadsheet which has some very simple formulas. As I changed some of the variables I noticed that the cells with the formulas are not updating automaticaly. If I highlight the cell and then position my cursor inside the formula bar then hit return the cell with update. Is there a protection or setting that I am unaware of that controls this behavier?

I am having a problem with a match function not updating. My workbook is set
to automatic calculation. This is the formula:
=INDEX('101 Competition'!$A$10:$FV100,MATCH($A$6,'101
Competition'!$A10:$A100,),MATCH(Q11,'101 Competition'!$A$10:$FV$10,))
The formula does not update. I hit F9 and still no update. But when you
highlight the cell, click the Fx button, and hit enter the cell updates
correctly. Why won't Excel automatically pull a value, or at least pull it
when F9 is pressed?

--
Paul

Hi,

I have two problems with a couple of macros I am writing.

1. I am updating a formula in several cells with a macro. The formulas calls an external application (Bloomberg) and gets data in columns in the form of a time series and associated data in the next column. The formula is correct so there is no problem there. If i delete the "=" sign before the formula press enter and then go back to the cell that contained the formula and reenter the equal sign in front of the formula and press enter excel accesses the external data. Any thoughts?

2. When I run my procedures several of them will change the contents of the worksheets. Some formulas get reference errors because of this so I have inserted macros to recreate the formulas. The only problem is that my procedures can't see the result of the updated formulas until the procedures have finished. The calculate command does not seem to help. How can I make my procedures see the result of different formulas while the procedure is still running?

Finaly I am new here - do I have to post these to questions in separate posts?

Great forum! I really like this site.

1. The Bloomberg application uses vba procedures to update excel. This means that the Bloomberg procedures cannot be activated while my own code is running. The workaround is to use ApplicationOntime to call my procedures after Bloomberg Refresh has been activated.

I have a spreadsheet with 97 individual tabs. One tab is the complete
report, the other 96 tabs are paste links pulling from the main spreadsheet.
I have the issue where the paste links on the 96 tabs will not update
automatically. Each cell must be double clicked on and then the info will
pull through correctly. Formulas are correct, and auto calculation is turned
on.

I have never had this happen on any other spreadsheet. Does anyone have
advice or encoutered a similiar situation?

Thanks

Hi,

I have array formula in my excel, i use ctrl,shift+enter to update the formula and values

Let say my value is Cell A1 and Formula in A2 and it works fine.

But if I change value in A1 it does not update in A2 automatically. It only updates when I save the file.

Not sure why it is doing this way

NKRA

Hi,

I cant figure out why I must update all cells, with formulas, manually after I do something with the workbook. I'll try to make an example.
If I make a macro that enters =sum("F1:F10") it says #NAME? when its done, but if I dubbleclick it and press enter it calculates the cell like its supposed to. I've tried to press alt + ctrl + F9, but that doesnt work.
Is there a command to dubbelclick the entire workbook and then press enter if you know what I mean?

This is really annoying, does anyone have any suggestions?

Thanks

Erik

I have several sheets rolling into a summary sheet. The data is by location
and data type. Using SUMIF referring to range names to get the numbers in
the right place. My formulas are not updating automatically though automatic
calculation is selected in Options.

In order to update the formulae, must go through each cell and F2 [ENTER].

I tried F9 and all the variations of F9 - does not work.

Anyone know what's wrong?

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.

Hi there

When I change a cell which should update a formula in another cell I have to save the file for it to update.
This happens me in some files in XL2007 and 2003. In some files but not in others.
Anybody know how I can fix this?

Any help appreciated.

Regards
Gusss

I was kindly given some code to count the incidence of a background colour in
a range of cells. Is there any way of having the answer automatically update
as now if I change a background colour the formula does not update
automatically. I'm pressing f2 on formula and pressing return to update but I
don't want to have to do this as there are many incidences of the formula.
Any help appreciated.

I am using Excel 2010 and have a workbook with two sheets. Sheet #1 is formatted in a printable form layout that is used by a department in our company and each cell on sheet #1 has a link to a cell in sheet #2. Sheet #2 is a simple table format (rows and columns). Sheet #2 is updated using a SSIS package. The whole process worked fine with Excel 2003 but now with Excel 2010 the Sheet #1 cells are not updating with the new values contained in sheet #2. I have to manually force the vallues to show up by clicking into each cell on sheet #1, pressing the F2 key then hitting the Enter key.

How can I get the referenced cell values to update automatically??

Hi

I have a worksheet in my Excel wookbook that references particular cells from another sheet. I use a simple macro to alter the values in some of these cells, but for some utterly bizzare reason, the cells referencing them are not updated. The only way to update the cells is to click on the cell and press Enter!

I've never had this problem before.

Does anyone know why this is, or perhaps know of a line of VBA code that updates every cell on a particular worksheet.

Many thanks
Johno

I don’t know if anyone can help me but this problem is frustrating me. I will try to explain as best I can. When I have a column of data, say reference
A1 to A9 with a formula beneath it in A10 being that the formula is =SUM(A1:A9) when I update any of the data in A1 to A9 the figure in A10 does not update. The only way to get it to update is to click in A10 where the formula is and then click in the formula bar and press enter; the formula seems to recalculate after this and it works or you need to save it and it works. It should work right after any change in the data being added, I don't know what is happening or has happened but any solution to this problem would be much appreciated.

Thanks

hi all,

i have some links pasted throughout a workbook but they are not updating automatically?

e.g. =Summary!$DR$15

but when something is entered into cell DR15 on the summary tab it doesn't automatically update?? to get it to update i have to click on formula & press eneter ... i haven;t had this happen before ... what am i missing?

I don’t know if anyone can help me but this problem is frustrating me.
I will try to explain as best I can. When I have a column of data, say
reference
A1 to A9 with a formula beneath it in A10 being that the formula is
=SUM(A1:A9) when I update any of the data in A1 to A9 the figure in A10
does not update. The only way to get it to update is to click in A10
where the formula is and then click in the formula bar and press enter;
the formula seems to recalculate after this and it works or you need to
save it and it works. It should work right after any change in the
data being added, I don't know what is happening or has happened but
any solution to this problem would be much appreciated.

Thanks:

--
cerpintax
------------------------------------------------------------------------
cerpintax's Profile: http://www.excelforum.com/member.php...o&userid=35411
View this thread: http://www.excelforum.com/showthread...hreadid=551805

*All of my formulas * are not updating automatically (SUMs, IFs, etc); I
have to go back to the cell and press "Enter" for them to update.

Why would this be happening...any ideas?

Thanks,
JC

--
carlyman
------------------------------------------------------------------------
carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440
View this thread: http://www.excelforum.com/showthread...hreadid=469981


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