Updating linked cells within a workbook, from worksheet to workshe

(Excel 2002) I have a 5G Excel workbook with around 40 worksheets in it.  
Many of the sheets contain cells that refer to values in other sheets.  I 
recently found that some of the values were incorrect (I believe they were 
just not updated).  When I double clicked on the cell, gained access to the 
formula and then hit enter, the cell updated.  I'm wondering 2 things - 

1) Under what circumstances will cells NOT automatically update?  I thought 
they always updated automatically as long as you're working within the same 
workbook.

2) Is there a way that I can update all cells at once?
0
3/3/2005 3:59:04 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
646 Views

Similar Articles

[PageSpeed] 10

is calculation set to manual or automatic (tools / option / calculation tab)

pressing the F9 key should update all formulas

Cheers
JulieD

"notloiseweiss" <notloiseweiss@discussions.microsoft.com> wrote in message 
news:F11F5387-8005-4C30-93D0-5E71864B5BB1@microsoft.com...
> (Excel 2002) I have a 5G Excel workbook with around 40 worksheets in it.
> Many of the sheets contain cells that refer to values in other sheets.  I
> recently found that some of the values were incorrect (I believe they were
> just not updated).  When I double clicked on the cell, gained access to 
> the
> formula and then hit enter, the cell updated.  I'm wondering 2 things -
>
> 1) Under what circumstances will cells NOT automatically update?  I 
> thought
> they always updated automatically as long as you're working within the 
> same
> workbook.
>
> 2) Is there a way that I can update all cells at once? 


0
JulieD1 (2295)
3/3/2005 4:04:18 PM
Hi,

Check the options: Tools -> Options -> Calculation.  You can apply settings 
that automatically calculates each time it registers a change to a value that 
is used in calculations.  Alternatively you can change it so that you have to 
manually tell it to calculate each time.

F9 re-calculates the workbook.

When you have such great capacity Excel may struggle to calculate - check 
your status bar and it will report back on how far through the calculation 
process it is as a percentage.

Cheers,
Jon

"notloiseweiss" wrote:

> (Excel 2002) I have a 5G Excel workbook with around 40 worksheets in it.  
> Many of the sheets contain cells that refer to values in other sheets.  I 
> recently found that some of the values were incorrect (I believe they were 
> just not updated).  When I double clicked on the cell, gained access to the 
> formula and then hit enter, the cell updated.  I'm wondering 2 things - 
> 
> 1) Under what circumstances will cells NOT automatically update?  I thought 
> they always updated automatically as long as you're working within the same 
> workbook.
> 
> 2) Is there a way that I can update all cells at once?
0
Springbok (23)
3/3/2005 4:11:04 PM
Thanks for the response.
I didn't know about this Tools -> Options -> Calculation setting, so it is 
useful to know.  Unfortunately, my setting is already on update.

I was aware of the 'status bar', so I don't think
that was it either...

"Springbok" wrote:

> Hi,
> 
> Check the options: Tools -> Options -> Calculation.  You can apply settings 
> that automatically calculates each time it registers a change to a value that 
> is used in calculations.  Alternatively you can change it so that you have to 
> manually tell it to calculate each time.
> 
> F9 re-calculates the workbook.
> 
> When you have such great capacity Excel may struggle to calculate - check 
> your status bar and it will report back on how far through the calculation 
> process it is as a percentage.
> 
> Cheers,
> Jon
> 
> "notloiseweiss" wrote:
> 
> > (Excel 2002) I have a 5G Excel workbook with around 40 worksheets in it.  
> > Many of the sheets contain cells that refer to values in other sheets.  I 
> > recently found that some of the values were incorrect (I believe they were 
> > just not updated).  When I double clicked on the cell, gained access to the 
> > formula and then hit enter, the cell updated.  I'm wondering 2 things - 
> > 
> > 1) Under what circumstances will cells NOT automatically update?  I thought 
> > they always updated automatically as long as you're working within the same 
> > workbook.
> > 
> > 2) Is there a way that I can update all cells at once?
0
3/3/2005 4:23:02 PM
Thanks for the response.
I didn't know about this Tools -> Options -> Calculation setting, so it is 
useful to know.  Unfortunately, my setting is already on update.

The F9 is also good to know about, although it sounds like I shouldn't need 
to use it since I already have automatic updates on...


"JulieD" wrote:

> is calculation set to manual or automatic (tools / option / calculation tab)
> 
> pressing the F9 key should update all formulas
> 
> Cheers
> JulieD
> 
> "notloiseweiss" <notloiseweiss@discussions.microsoft.com> wrote in message 
> news:F11F5387-8005-4C30-93D0-5E71864B5BB1@microsoft.com...
> > (Excel 2002) I have a 5G Excel workbook with around 40 worksheets in it.
> > Many of the sheets contain cells that refer to values in other sheets.  I
> > recently found that some of the values were incorrect (I believe they were
> > just not updated).  When I double clicked on the cell, gained access to 
> > the
> > formula and then hit enter, the cell updated.  I'm wondering 2 things -
> >
> > 1) Under what circumstances will cells NOT automatically update?  I 
> > thought
> > they always updated automatically as long as you're working within the 
> > same
> > workbook.
> >
> > 2) Is there a way that I can update all cells at once? 
> 
> 
> 
0
3/3/2005 4:25:09 PM
I've never seen this happen in real life, but there have been a few posts that
describe your problem.

One suggestion is to try to wake up excel's calculation engine (may be the wrong
term!):

Edit|replace
what:  = (equal sign)
with:  = (equal sign)
replace all

And under the Options button, you can choose Workbook so that you don't have to
do each worksheet individually.

There have been replies that say this works, but also questioning why this
happens and will it fix it forever.

I don't have a guess for either question.

notloiseweiss wrote:
> 
> (Excel 2002) I have a 5G Excel workbook with around 40 worksheets in it.
> Many of the sheets contain cells that refer to values in other sheets.  I
> recently found that some of the values were incorrect (I believe they were
> just not updated).  When I double clicked on the cell, gained access to the
> formula and then hit enter, the cell updated.  I'm wondering 2 things -
> 
> 1) Under what circumstances will cells NOT automatically update?  I thought
> they always updated automatically as long as you're working within the same
> workbook.
> 
> 2) Is there a way that I can update all cells at once?

-- 

Dave Peterson
0
ec357201 (5290)
3/3/2005 7:13:20 PM
Reply:

Similar Artilces:

RMS updates
I purchased RMS this past January and have been running since February. Are there any upgrades...and if so, how do I get them? Also, how to I increase the size of my SQL DB? It seems it's at 94% and losing the DB would be a disaster. Thanks I believe you are suppose to be notified by email or possibly snail mail when updates come out. They get sent right to you. Of course I have yet to see them either. As for the database it should be set to grow automatically. "zoostation" <zoostation@discussions.microsoft.com> wrote in message news:55BD57F7-B22B-45E1-9BEF-EF0...

Can i use Windows Search to find a number within Excel?
If i use Widnows search of the CONTENTS of files (not file names), i find in Excel, that for cells that contain text, they can be found. But, it does not appear to me that numbers can be found. Of course the number as Excel stores it may differ from how it is displayed. But, does anybody know if this is possible? For instance, i have a cell with the number 3,709,227 in it, as it appears in Excel (as formatted). I tried typing the number both with and without the commas, into WIndows search, but Windows search still does not find the Excel file that contains that number. So, any kn...

How: Link Word merge document to Quote from 'print quote for custo
In CRM 4.0 we want to do this as sometimes the sales person does minor amendments to word merge document, and if customer looses quote and wants another sent, any member of staff can send them a copy again. Currently the workaround is to save to Word document and then add Note to Quote and then attach Word file manually. Is there a better way than this? ...

DST update tool for Outlook/exchange...
Hi all, How do i check if the patch for outlook software applied and work? After applied all the patch for server, exchange, and Outlook/exchange update tool, will the appointment shift by 1 hour or it will stays at the same time as of NOW before 3/11? THanks for your time. Only the appointments in the affected DST period will be impacted - which occurs 3 weeks earlier and ends 1 week later than earlier years. The Outlook Timezone Update Tool in interactive, allows selecting/deselecting appointments to be changed and outputs a log that is displayed at the end of the session. When you...

how to count cells with equal sign
Hi guys please help me to write this formula: if the integers of the A1 and A2 are of the same sign(+ or -) =1 if not =0 I need to count cells with equal sign and i don't know how to expres in formula "equal sign" Appreciate your help -- Message posted from http://www.ExcelForum.com Hi! Perhaps use the fact that, if you multiply together two numbers wit the same sign the answer will be positive. And if you multiply two wit opposite signs, the answer will be negative. So: =if(A1*A2>0,1,0). This doesn't cover the cases of A1 and/or A2 being zero. Does tha matter? If i...

putting date and time together in 1 cell
Hi I need to convert New Zealand time to UK time, the time is displayed in two parts A B date time dd/mm/yy hh:mm:ss How do i get cell A and cell B into one cell with the format dd/mm/yy hh:mm:ss and then I can just minus 13 hours. any ideas please. =SUM(A2,B2,-13/24) and format the cell as dd/mm/yy hh:mm:ss. HTH Jason Atlanta, GA >-----Original Message----- >Hi I need to convert New Zealand time to UK time, the time >is displayed in two parts >A B >date time >dd/mm/yy hh:mm:ss > >How do i get cell A and c...

Formatting 0 values to show blank cells
I am using the =IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)) to return a zero value. For printing purposes I need the 0 to not show in the cell (blank cell). I can do this by using the accounting format, but a dash (-) still shows in the cell. The sheet is protected to protect the formula. How can I protect AND not show anything in the cell WHILE keeping the value at "0"? You can use set the custom format to ;;""; See Worksheet and Excel table basics > Formatting numbers in Excel help file for details. "RLD" wrote: > I am using the =...

Newbie struggling
Hi For coursework I have to create an excel workbook that takes totals from invoices and adds them all up - pretty simple for some but not me! I have created the invoice template and the user enters the necessary data to complete and print it out - what I need is a way of taking four or five sets of data from that invoice sheet (e.g. invoice number, customer name etc etc) and writing them to another sheet so that I can add them up. The problem I am having is how to get that data into the next blank row in the second sheet - currently it only overwrites the first set - how do I get the pointe...

Copy formulaS between Workbooks
This might be some sort of follow up question from Ken Runge post "Cop formula between Workbooks" from Oct-23-2003. I would like to copy som formulas from one workbook to another without getting a reference t the original workbook ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Syslock, This happens, I think, where there are cell references to other sheets. One way is to copy the text of the formula (drag across the text in the formula bar), instead of the cell...

How to change a Macro based on content of a cell
Hi, I need to import a text file into a spreadsheet. I can do this manually by doing File > Open > then select a text file. Then Excel goes through the Text Import Wizard I can automate this by recording a Macro in Excel. The macro looks like this (Excell wrote that for me, I did not): Sub Macro1() Workbooks.OpenText Filename:="C:\DrillData\" + "MOR701.pck", Origin:=xlMSDOS, _ StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(12 _ , 1), Array(26, 1), Array(37, 1)), TrailingMinusNumbers:=True End Sub How can I change ...

Updating Rank
I have a spreadsheet with column A ranking the info 1-100 for priority. How can I change a row to move to the top and re-rank the rest? The best example I have seen of this is your netflix queue where you ask it to move a selection to the top or re-rank numbers by changing where you want them in the queue. I hope this makes sense. Thanks!! 'Right click on sheet tab, view code, paste this in: '===================================== Private Sub Worksheet_Change(ByVal Target As Range) 'Define Range that will be changed If Intersect(Target, Range("A1:A100")...

how to sort cells with sheet reference
i an unable to sort the data in cells containing a reference to anothe sheet. it seems as though excel is treating this reference as absolut and not relative. is there any way to do this, such that thes equations adjust to the sorting? any thoughts would be appreciated. thanks -- vba_neophyt ----------------------------------------------------------------------- vba_neophyte's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2418 View this thread: http://www.excelforum.com/showthread.php?threadid=37804 one way would be to copy and paste specail values only - a...

Excell Cells: Auto Formatting
I have protected a workesheet and unfortunately when the people submit information on the cells and it is too long than the actual height of the row and it wont extend. What can I do? When protecting the sheet allow users to format cells and rows. Gord Dibben MS Excel MVP On Tue, 9 Feb 2010 13:14:01 -0800, Sophie <Sophie@discussions.microsoft.com> wrote: >I have protected a workesheet and unfortunately when the people submit >information on the cells and it is too long than the actual height of the row > and it wont extend. What can I do? How do I jus...

Linking Powerpoint and Publisher publications
Right Im trying to produce a Business plan (sadly im not away to become a rich and successful entrepreneur, this is all coursework for university!) and a presentation to go with it. I would quite like the business plan and the slideshow to have a matching theme, the same background, graphics etc but how do I do that?? Any ideas?? I just want it to look resonably professional >-----Original Message----- >Right Im trying to produce a Business plan (sadly im not away to become a >rich and successful entrepreneur, this is all coursework for university!) and >a presentation to...

range to cell
Hello, I am writing a program. I have a excel range object. And I want to point another cell at this range. how can I do that? On the range there is column = 4 row = 3. but I cannot just say =4,3 or something like that? don't you need =D3? why is the column D and not 4? strange =sheets("Sheet1").range("D4") or =sheets("Sheet1").cells(3, 4) -- HTH... Jim Thomlinson "greg" wrote: > Hello, > I am writing a program. > I have a excel range object. > And I want to point another cell at this range. > how can I do that? > On the ran...

Rounding issues after 2010 updates installed
I am having issues with the system rounding for AP invoices in "shipment/invoice" receivings, allocated to WIP accounts. Before, we used to have 4 decimal places to round and if something wouldn't match up, we would enter the extended cost of an item, the unit cost would adjust (even if we didn't see anything) and everything would be fine. Now we only have 2 decimals places (for some crazy reason) and are having issues when a vendor rounds to .0000 and computer only goes 2 decimals. We get an error message that WIP does not equal AP amount and there is no wa...

if the cell value exceeds the limit to have a dialog box appear
Is there a way that, if the cell value exceeds a certain limit, a dialog box would appear? Thanks for any assistance. Dave Check Data Validation: Select the cells Data>Data Validation Allow: (check out the number options: whole number, decimal, etc) Does that help? *********** Regards, Ron "Dschro" wrote: > Is there a way that, if the cell value exceeds a certain limit, a dialog box > would appear? > > Thanks for any assistance. > > Dave > Ron, Here's the scenario -- on an Expense Report: If an employee was to fill in any amount in the &...

DDE link error Excel freezes
Hi, I am using Excel 2003 and in one sheet I have a DDE link from anothe program. When I open the sheet I get a question wether I want to updat the fields or not. When I click YES the sheet normally opens and I ca view the data. But sometimes it seems like Excel freezes and I have t wait 3-5 minutes until it resumes. Anyone that has any idea why? The source program is always started first by the way. Kennet -- Message posted from http://www.ExcelForum.com ...

before update
Hi everyone. I am using the following code: Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.Combo60 = True And (Nz(Me.Personal_Data_Inaccuracies, 0)) + (Nz(Me.Improper_Combine, 0)) = 0 Then MsgBox "You selected yes to the Maintenance Issue question, therefore please select the number of errors in that section", vbCritical Cancel = False ' Stop the process End If End Sub If you try and save it prompts for the msgbox one time. How can i make it so that the message comes up until they enter the required data or change the field to false? Thanks. Cancel = False...

Sorting protected worksheet
Is there any way to sort a protected worksheet? I looked thru the posts here and found where someone said to create a macro that unprotected the sheet, then sorted the data, then protected it again. I tried that, but everytime I try to create the macro I get this error "Project Locked - Project is unviewable". Any suggestions? If the workbook is shared, you won't be able to unprotect it. Perhaps that's the problem. Phyllis wrote: > Is there any way to sort a protected worksheet? I looked > thru the posts here and found where someone said to create > a m...

Excel cell display
I want a text box to display when I click on a particular cell - how can I make that happen? Would Insert> Comment work for you? "slantt" wrote: > I want a text box to display when I click on a particular cell - how can I > make that happen? Yes, thanks - I found that after I posted of course (newbie to Excel). I was looking to see if there was a macro I could gen to do the same thing... "ufo_pilot" wrote: > Would > Insert> Comment > work for you? > > > "slantt" wrote: > > > I want a text box...

Printing Home Inventory Worksheet Report in Money Deluxe 2005
Can anyone tell me how to print a "home inventory worksheet report" or export it to Excel in Money Deluxe 2005? I have printed this report in the past with Money 2004 Standard, but can't figure out to do so in 2005 and neither can tech support. Maybe i'm missing something obvious, but when I am in the home inventory, I can click file->print (this is deluxe 2005 uk edition) -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny or http://money.mvps.org/faq for UK tips and fixes for MS Money. To...

Quickly locate a specific page/tab in workbook
I have a workbook with over 1000 pages/tabs. Is there a command to quickly access a specific page/tab without scrolling through the entire series to get to the one I want? One way: In pre xl 2007 ( might be the same in xl2007 I just don't know) Right click on the arrows in the lower left corner of your worksheet, (to the left of the tabs) and you will get a list you can scroll thru to find the worksheet you need to go to. Mike Rogers "touchstone" wrote: > I have a workbook with over 1000 pages/tabs. Is there a command to quickly > access a specifi...

worksheet linkage
hi, The details is please see teh attached file, you can see on the file Worksheet B of B62, I typed 15254", it col name "yarn count" and the product code you can see is Q921 at cell A62. Worksheet A, you can see F2 is Q921, col name is "product code". Now I just plugin a vlaue B62 ='15254' and I want it shows on workshee A - G2 but remember it must match the product code of worksheetB - cellA6 'Q921' and worksheet A cell F2 'Q92' because next time user will plugin a number ie.1111 on B164; wher product code of worksheet B - cell A164 mu...

Refresh ODBC link
I have a link set up to pull information from our system directly to an Access table. Our systems department has added a couple of new fields to the table but it doesn't automatically update my Access table. Is there a way to refresh the table to get these new fields, or do I have to set up a new table? Thanks. Go to the linked table manager (Tools/Database Utilities), open and select your table, then refresh the link. Milton Purdy ACCESS State of Arkansas "nitengale" wrote: > I have a link set up to pull information from our system directly to a...