Using iterations to calculate circular references.

I am working with data that requires using circular references in some 
calculations. I checked the "iterations" box to allow for these calculations 
to work, and Ecxel seems to be calculating everything correctly. The problem 
is that after I close the workbook, when I later reopen it to continue 
working, all the cells that are a part of the circular reference display the 
#VALUE! error. I can fix this by deleting one cell that is part of the loop 
and then undoing that command, or by copying the formula from a cell, 
deleting it, and pasting it back into the same cell. I have to do this every 
time I open the file. I tried recording this method into a macro, but when I 
run it it takes a while since there are multiple sheets with lots of similar 
calculations. Is there a better way to make Excel calculate correctly As soon 
as I open the workbook?

Thanks,
David
0
dave871 (680)
8/18/2004 6:57:03 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
544 Views

Similar Articles

[PageSpeed] 50

Hi David,

Do you use manual calculation? If so, did you check the "Recalculate before
save"  box?

-- 

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Dave" <Dave@discussions.microsoft.com> wrote in message
news:93CBD4AD-7F39-4DB5-B714-EBA80CD3ACB6@microsoft.com...
> I am working with data that requires using circular references in some
> calculations. I checked the "iterations" box to allow for these
calculations
> to work, and Ecxel seems to be calculating everything correctly. The
problem
> is that after I close the workbook, when I later reopen it to continue
> working, all the cells that are a part of the circular reference display
the
> #VALUE! error. I can fix this by deleting one cell that is part of the
loop
> and then undoing that command, or by copying the formula from a cell,
> deleting it, and pasting it back into the same cell. I have to do this
every
> time I open the file. I tried recording this method into a macro, but when
I
> run it it takes a while since there are multiple sheets with lots of
similar
> calculations. Is there a better way to make Excel calculate correctly As
soon
> as I open the workbook?
>
> Thanks,
> David


0
nicolaus (2022)
8/18/2004 7:11:00 PM
No, I am using automatic calculation. I tried using manual calculation, but 
it did no good. 

"Niek Otten" wrote:

> Hi David,
> 
> Do you use manual calculation? If so, did you check the "Recalculate before
> save"  box?
> 
> -- 
> 
> Kind Regards,
> 
> Niek Otten
> 
> Microsoft MVP - Excel
> 
> "Dave" <Dave@discussions.microsoft.com> wrote in message
> news:93CBD4AD-7F39-4DB5-B714-EBA80CD3ACB6@microsoft.com...
> > I am working with data that requires using circular references in some
> > calculations. I checked the "iterations" box to allow for these
> calculations
> > to work, and Ecxel seems to be calculating everything correctly. The
> problem
> > is that after I close the workbook, when I later reopen it to continue
> > working, all the cells that are a part of the circular reference display
> the
> > #VALUE! error. I can fix this by deleting one cell that is part of the
> loop
> > and then undoing that command, or by copying the formula from a cell,
> > deleting it, and pasting it back into the same cell. I have to do this
> every
> > time I open the file. I tried recording this method into a macro, but when
> I
> > run it it takes a while since there are multiple sheets with lots of
> similar
> > calculations. Is there a better way to make Excel calculate correctly As
> soon
> > as I open the workbook?
> >
> > Thanks,
> > David
> 
> 
> 
0
dave871 (680)
8/18/2004 7:25:04 PM
Reply:

Similar Artilces:

Unwanted Calculation
Excel 2003. I am using a VBA procedure to step through a worksheet from bottom to top with a for-to loop, looking at the cell in column A for each row. If column A contains different text than the previous cell in column A, I insert a row about that one with text to indicate what the numbers in each cell refer to. To speed up the process, I use Application.Calculation=xlManual before starting the loop. As the loop runs, Excel flashes "Ready" and "Calculate" in the statusbar. Since the statusbar says "Calculate", I presume it really is recalculating the...

Can we use group policy templates for Outlook client options
Hi there, We are looking to automate the CRM outlook client settings so we don't have to manually change them for each user. Is there any way we can do this through group policy or another way? It would be handy if we had a list of registry settings that we could use? Thanks ...

Unable to use Word as email editor with outlook 2003 and exchange server
I am Unable to use Word as email editor with outlook 2003 and exchange server. Does anyone have a reason for this and/or a work-around so that I can use MS Word as the email editor. I have reviewed the knowledge base and cannot find a solution. Please help. Thanks, HR HR <anonymous@discussions.microsoft.com> wrote: > I am Unable to use Word as email editor with outlook 2003 > and exchange server. Define "unable". What happens when you try. What version of Word? -- Brian Tillman ...

How can I cross reference a SmartArt Graphic to a page/text?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am trying to allow users to click on the SmartArt Graphic on my first page and automatically go to the respective page within the document. I click next to the wording on the graphic and go to Insert, but it will not allow me to insert the cross reference. You can do it the other way around - from the text to the graphic, but not from the graphic to the text. Anyone know a way around this or another way to do it? Objects cannot be used as active hyperlinks in a Word document. You can do what you describ...

How can I put text in a circle using Publisher ?
Have looked everywhere for an answer to this ? : How can I put text in a circle while using Publisher ? If I want to surround a shape or piece of clipart with words, I often need to have text in a circular or oval shape ........ surely this can be done with Publisher. Thanks for any expertise. DixieWins wrote: > Have looked everywhere for an answer to this ? : How > can I put text in a circle while using Publisher ? > > If I want to surround a shape or piece of clipart with > words, I often need to have text in a circular or oval > shape ........ surely this can be ...

Using the mail merge function from excell
Is there a way of using the mail merge function in word to set up a three column format of names and addresses. I need to do this without using the "label" format - as I need it in word domument format in order to be able to add and make changes in word. Thanks Sheila You may want to look into storing your data in excel (already has nice columns and rows) and using MSWord's builtin MailMerge to create the labels. You may want to read some tips for mailmerge: http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge The first is from Davi...

how to use TrackPopupMenu last parameter
Hi, I need to make a menu that does not disappear. (Odd, yes. I need to let users edit menu resources at runtime and would like to display the menus as how they would actually look.) So I'm looking at CMenu's TrackPopupMenu function, whose last parameter is LPCRECT lpRect. I figure if I set this rectangle to be (0, 0, screen width, screen height), then even if the user clicks elsewhere on the screen, the menu won't disappear, right? I tried the following code and the menu still disappears when I randomly click elsewhere in the application. What am I doing wrong? void CMenuVi...

Viewing OWA using the Outlook Full Client Browser
I just tried to access my corporate email account using OWA from within the Outlook full client running on my personal machine. I find it quite funny that OWA serves up its non-IE version when displayed using browser built into Outlook. Not really considering Outlook is running in a much higher security configuration than Internet Explorer and has a lot of features disabled. Just curious; why are you accessing the OWA website via Outlook? Ask your email admin if they also offer direct access to the corporate email servers via Outlook Anywhere (RPC over HTTP). -- Robe...

Are user certs broken when used on a front end server?
Does a front end exchange 2003 SP2 server support requiring a user certificate? On the back end server requiring user certs works fine (but breaks Treo syncing). I therefore set up a front end server and two virtual servers (one just SSL for treos and the other for Outlook Web Access). If I require a cert on the front end virtual directory I want to use for Outlook Web Access and try it, IE presents a blank box for the user certificate (when it should present one of the installed user certificates). If the backend is configured the same way and a client connects, their user certs are display...

word freezes periodically when i am tracking changes, using endnote and inserting pictures from file
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel word freezes periodically when i am tracking changes and using endnote and when i am inserting pictures from file. i seem to have no other options but force quit then i get lots of recovered documents. i am doing doctoral study so my documents have endnote field notes and changes highlighted. <br><br>inserting pictures from file into a word document causes instant hanging. Judging from the array of causes you're listing I believe there may be several things that need to be addressed. Sinc...

can I use Publisher to set up signatures of 8 pages?
I neeed to print and bind a book. I can't find any way to set up and print in sgnatures of 8 or so pages. I seem to be limited to 4 page bookfold. Any ideas? Insert, page -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "traveller" <traveller@discussions.microsoft.com> wrote in message news:9FD3B5CB-8D56-49DE-B632-06617A54EBE7@microsoft.com... >I neeed to print and bind a book. I can't find any way to set up and print in > sgnatures of 8 or so pages. I seem to be limited to 4 page bookfold. Any > idea...

Open New Visio Drawing using VBA in Word?
Hi, We're creating new Word 2007 templates, and some of them require flowchart functionality from Visio. Our goal is to create a macro within the Word template to call Visio, open a new drawing and have a custom stencil set open for users to use. Users would then open create their flowchart/diagram in Visio, and it would update automatically into the Word template when Visio is closed. I'm using the following code to start Visio, however I'm stuck on where to go from there: Sub Visio() Shell "C:\Program Files\Microsoft Office\Office12\VISIO.exe", vbN...

Need to format text in header, but value is generated using VBA
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C6360C.2372C770 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I wanted to reference a cell value in my spreadsheet header and found = out how to do that using this function: Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim WS As Worksheet For Each WS In Worksheets ActiveSheet.PageSetup.RightHeader =3D _ Format(Worksheets("Time Period Info").Range("B3").Value) =20 Next WS End Sub However, this leaves my text for ...

Can't e-mail from Word 2000 using Outlook 2002/XP
After "upgrading" Outlook 2000 SP-1 to Outlook 2002, I can't create a Word letter from within Outlook or send a file to a recipient from within Word. When I try to create a letter from within Outlook, I get the error "To use this feature you must install the version of Microsoft Word that matches the installed version of Microsoft Outlook". I've tried The Outlook 2002 "repair", and even uninstalled it and re-installed Outlook 2000 SP-1. In that configuration, the software worked fine. But when I "upgraded" again to Outlook 2002, it was br...

(MFC) How to use messages to communicate between CMDIFrameWnd and CScrollView?
Alright, so I tried to use AfxGetMainWnd() from inside a CScrollView-derived class to access a function inside of a CMDIFrameWnd-derived class. It worked under the "Debug" configuration, but I'm getting a nasty error under the "Release" configuration: Coil error LNK2019: unresolved external symbol "public: void __thiscall CMainFrame::setCurrRef(class ATL::CStringT<char,class StrTraitMFC<char,class ATL::ChTraitsCRT<char> > >)" (?setCurrRef@CMainFrame@@QAEXV?$CStringT@DV?$StrTraitMFC@DV?$ChTraitsCRT@D@ATL@@@@@ATL@@@Z) referenced in function &...

After retreiving from back up, I cannot use the button send and re
Note the subject. I just recovered my PC. I get all my files from back-up and was plesantly surpriced that even my accounts where filled in. But it looks like there's something wrong with Windows mail since I cant use the button sent and recieve anymore. I can use the button for each account separately and for sent or recieve but not the combibutton thats in the menu. Does someone have a sollution to this problem?? Karin "Muisje70" <Muisje70@discussions.microsoft.com> wrote in message news:BEAE122D-2D48-40DE-B6E4-B472D81031C9@microsoft.com... > Not...

Getting info from the last used cell
Currently I am adding many sheets together. I am using "Start Sheet" and "End Sheet" and everything in between those two sheets is adding for a total. I would (in a few cells) to just get the data from the last cell used. Example: The sheets I am adding are dates and if sheet 01-19-09 (dd-mm-yy) has the last entry in cell c18 I would like that number. However there may be blank cell before that sheet. and after that sheet there will be info in other cells. I would like that info to go into c18 of the sheet I use and named "Totals". -- Ed Davis ...

Can I use Windows XP Home for Commercial ?
i would need to clarify on this matter whether can we use Windows XP Home edition for a commercial ? is it legal ? a lot of people told me that it is okay using Windows XP Home for commercial but it just that we cannot fully utilize the feature of the Windows i.e. joined domain ...etc I also cannot find anything in Windows XP Home EULA that the Home edition is only for NON-Commercial use. Can anyone clarify this to me ? Jim wrote: > i would need to clarify on this matter whether can we use Windows > XP Home edition for a commercial ? is it legal ? a lot of people > told ...

Can I use MFC classes in a piecewise fashion
I am making a DirectX-based 3D game. There is virtually no windows stuf apart from setup so switching to MFC completely seems pointless. However some of the MFC classes (mainly CString) are useful, can I use these without linking the MFC dll? Or if there is a standard string class elsewhere which is as useful that would do - does the STL include this? It is a lot easier in VS7 than in VS6; MFC has been split up a bit better. Otherwise, the STL std::string is what you would use. joe On Thu, 20 May 2004 01:36:02 -0700, d000hg <anonymous@discussions.microsoft.com> wrote: >I am ma...

Previously used/opened record
I would like to have a button to hit to get me to my previously opened/used record. Can anybody please oblige me with some code to achieve that? Thanks, RF. Define "previously"... No, seriously, until you can tell Access HOW to find the "previous" record, Access won't know what to do. By "previously opened/used" do you mean "most recently" (a single record) or ALL records that have been opened/used. If the latter, since when? If a record is only "opened" (?looked at but not changed?), does that count? What do yo...

Is there any way to use the MATCH function with more than 1 column
For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value 215, but my data set spans more than one column. My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) returns an #N/A error. What can I do? Do you want both the row and column ? Two separate values? -- Gary's Student "Mr. Snrub" wrote: > For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value > 215, but my data set spans more than one column. > > My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) > returns an #...

Install dates formating using conditional formating?
I have 2 cells (A1 and A2). I would like to have the following take place. 1. If I place a date that is either past or withinthe next 30 days into A2 format the text as red. 2. If I place any date into cell A1, I need A2 to say N/A. I have been able to do #1 using conditional formating but am stuck on #2. Can anyone help? Thanks -- Jerry Eggleston ------------------------------------------------------------------------ Jerry Eggleston's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9898 View this thread: http://www.excelforum.com/showthread.php...

Is Outlook free to use with the Microsoft Office 2003 I bought?
I bought Microsoft Office 2003 after buying my computer. It has Microsoft Outlook as part of the package. I have an internet provider already, but really like Outlook after having used it for years at work. Can I use Outlook for free? Yes. Just install it and set it up and use it -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "KatsEmail" <KatsEmail@discussions.microsoft.com> wrote in message news:93C001DC-7972-496D-88B6-1506F7982F6C@microsoft.com... >I bought Microsoft Office 20...

cell reference #2
I have a list of numbers in column A. I wish at the bottom to return the maximum value in column A using =max(a1:a100) but i also want the cell reference of this value. how can i do this. thanks Use the MATCH function - it will give you the relative position of the maximum value in your range, and as this begins at A1 then this is the same as the row. If you have two maximum values, however, it will only report the first occurrence. Hope this helps. Pete lees news wrote: > I have a list of numbers in column A. I wish at the bottom to return the > maximum value in column A using =m...

How to use count with multiple conditions
I have a table in Excel: The first row is time in years. The second row is method name,say,"A","B","C". I want to count the number when the time is less than 5 years AND "A" method is adopted. I tried this: count(if(AND(C2:Z2<5,C3:Z3="A"),C2:Z2) but it didn't work. Any suggestion on how to revise the formula? Thanks! In the mean time, count(if(C2:Z2<5,C2:Z2))worked as well as countif(C2:Z2,"<5") Ming =SUMPRODUCT(--(C2:Z2<5),--(C3:Z3="A")) -- HTH RP (remove nothere from the email address if mailing...