Self Referencing If Statements, Iterations

Previously, I created several self referencing If Statements that
would return data when a toggle cell was equal to what I was looking
for and would not change if the toggle was equal to something else.
This had the effect of providing a data table without having to have
the input functions on the same tab as the table. For example, on an
excel merger model, on a cases tab I had many different "cases" (one
company acquired another at a 10% premium, 15% premium, 20%, 25%, 30%,
etc.). On another tab in cell A15, I had this if statement =if(CASES!$A
$1=A$14,RESULT,A15) then I would have this same if statement copied
and pasted throughout a range that looks like a data table. The result
being that I got a data table output without having to create a data
table since the inputs are on a different tab.

My problem is that in Excel 2003, excel seemed to run iterations until
the problem was solved, now I have to go to the cases tab to toggle
through the different options. I was wondering if there has been some
kind of functionality switch taht has caused Excel 2007 to stop doing
this. Iterations have been turned on and have ranged from 1 iteration
to 10,000. Any help would be useful.

As an aside, I cannot move the inputs to my tab or link the Cases tab
to my tab. This is not an option. Does anyone have any experience with
self referencing If statements?
0
chrisdorn (1)
8/25/2008 4:16:33 PM
excel 39879 articles. 2 followers. Follow

0 Replies
383 Views

Similar Articles

[PageSpeed] 20

Reply:

Similar Artilces:

Help requested for nested conditional formulas referencing other c
Hello--i can better explain the problem with an example: 24h 1wk change Person A 1 0 -1 Person B 0 1 1 Person C 0 0 0 Person D 0.25 0.25 0 Person E 0.5 Person F 1 0.5 -0.5 Person G 0 0.75 0.75 What I am trying to do is make three equations: 1 to calculate the average of the values in the 24h column, IF the value is NOT equal to zero, which I have accomplished with the array formula: {=AVERAGE(IF(B2:B8>0, B2:B8, ""))} here's where things get difficult--now, I want to find the average of the values in the 1wk column, only if either the values in the 24...

Referencing a Control on Another Form
I'm working with packaged software where the form I'll call frmExample consists of 2 headers and 2 details, called Header0, Header1, Detail1, Detail0. The CustomerID field (a combobox) on Detail1 is bound to the tblCustomer table, and the FinanceChargeAmount field on Detail0 is bound to the tblInvoice table. The customer ID is typed in or selected from a drop-down list. What I want to do is make FinanceChargeAmount.Visable either true or false based on a bit field in the Invoice table, called FinChgYn. The problem is, there is no "event" on Detail0 upon which to:...

STL iterators for MFC collections
Has anyone written STL-style iterators for the MFC collections CArray, CList, CMap? Can you point to any freely available implementations? Eric B Eric Beyeler wrote: > Has anyone written STL-style iterators for the MFC collections CArray, > CList, CMap? Can you point to any freely available implementations? > I've seen such adaptors at http://synesis.com.au/software/mfcstl/ (note that I've never actually used that library, so I can't say much about it) -- Regards, Tobias Tobias G´┐Żntner <fatbull@users.sourceforge.net> wrote in message news:<cg31og$5ib$07$1@...

Case statement comparing two ranges
Dear All I have writtent some code but I belive I have done it the long way and think there is a much simpler way of doing things. I am trying to compare two ranges in a case statement. Below is the code, can anyone help with reducing the code required or a more efficient way of doing it Tahnsk Regards Private Sub Worksheet_Change(ByVal Target As Range) Dim rngData As Range, rngCell As Range Dim Wday1data As Range, Wday1Cell As Range Dim icolor As Integer Dim WrkDays As Date Dim StartDate As Date Dim Enddate As Date Dim Day1 As Integer Dim icolor2 As Integer Dim i...

How do I turn off relative cell referencing in excel?
Relative cell referencing is currently active. How do I revert back to the normal cell referencing style of A1 etc. <Tools> <Options> <General> tab, And *uncheck* "R1C1 reference style". -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Glenn" <Glenn@discussions.microsoft.com> wrote in message news:306F6167-4596-4C05-8E59-83C1803743DB@microsoft.com... Relative cell referencing is currently active. How do I revert back to...

Bug: Cannot import brokerage statement
I just installed Money 2005 and then added my 401k account (Prudential Investments). When it asked me what transactions I wanted to download, I selected all. After the download finished I go to the Review investment activity page. I see a list of all the investment activity. I click the Next button to update Money to this statement. I get a dialog stating that a security was found where it already exists but under a different name. There are two radio buttons, the first to confirm that it is the same security, the second to add it as a new investment. I select the first(default) and the...

a statement on multiple backups
I've always stated that having multiple backups is a good idea, well last week I found out how good of an idea it was, I had recently installed a new dual core cpu ie faster better etc. I made a backup before I did the upgrade, I also installed a new larger hd for primary drive C, well to my sad suprise I found my latest update to my backups corrupted everything, and I had only one copy of my backups ie Image of Drive C. anyway I ran into multiple issues, bad SATA cable, etc, it's taken me all week to get it up and operating, when Id rather be outside playing in the sunsh...

What are the {'s either side of my if statement?
when I edit the cell they disappear and my formula display #value! eg: {=IF(SUM(IF(Tracking!$G$17:$G$9895="NB", IF(Tracking!$P$17:$P$9895="X",1,0),0))>0, SUM(IF(Tracking!$G$17:$G$9895="NB", IF(Tracking!$P$17:$P$9895="X",1,0),0)),"")} This means that you have an array formula, that is it is using what are typically single cell functions against an array of cells. The { } are just a way of showing in the formula bar that it is an array formula. An array formula is committed using Ctrl-Shift-Enter, not...

Crash when printing one specific client statement
Has anyone had a problem with one specific client becoming corrupted so you cannot print their statement? -- Christopher Fazio VMD Chris Fazio | 2009-03-01 | 5:29:32 PM wrote: >Has anyone had a problem with one specific client becoming corrupted >so you cannot print their statement? I have not seen that problem. I assume you're using one of the Business versions of Money. If you give us specifics of your issue, maybe we can help figure it out. Tell us things like: * Type of hardware * Operating system version * Version of Money (Help > About) * Exactly what happens ...

Can download web statements from one user login but not another in XP
When I login into money as user1 I can download my credit card statement into my without a problem. But when I am logged in as user2 the downloads will not work. The computer quickly does something when I initiate the download but I don't get and error message and nothing happen. Note the same file is used on both login cases? Any help out there?? ...

Header and cell referencing
My issue is this: I have 6 sheets. The first sheet contains the clients information. I want to take the last name of the client from the first sheet and have it appear in the header of every sheet after it. How do I create a cell reference inside a header? David David Sub Path_All_Sheets() Set wkbktodo = ActiveWorkbook For Each ws In wkbktodo.Worksheets ws.PageSetup.RightHeader = Sheets("Clients").Range("A1").Text Next End Sub Assumes client's name is in A1 Note: you can do the same thing by right-click on first sheet tab and "select all ...

Credit Card Statement Download M05...
I am having an issue with my GM Credit card. I have it set up to download the statement every 2 days. I also have some recurring bills set up to automatically enter them into my credit card account (such as the estimated payment for this card so my balance forecast for my checking account is accurate). When Money downloads the statement, it doesn't try to match the transactions. My checking account downloads the statement every day, and it does match the transactions. It just adds another transaction for the same amount. Is there a setting for each account as to how to handle ...

emailing statements #2
Does GP 9.0 only work with Adobe for this feature? If so, which feature? Can one license be used across the network, or do I have to buy a license for each user that may want to email a customer statement? It does. MS seems to have married itself to Adobe for some reason. You will need an Adobe license for each person who will use it. -- Charles Allen, MVP "Dwayne" wrote: > Does GP 9.0 only work with Adobe for this feature? If so, which feature? > Can one license be used across the network, or do I have to buy a license for > each user that may want to email ...

Referencing cells
I have an excel file with many spreadsheets (tabs). I need to take certain totals from each spreadsheet and put them into a summary spreadsheet. My question is: Is there a quicker way to do this than typing = and then going to the cell on the appropriate spreadsheet and hitting enter. I hope this makes sense. Thanks cate If the total cells are same cell on each sheet you can use the 3D formula =Sheet1:Sheet34!D16 If not D16 on each sheet, the method you describe is the only way other than typing the Sheet and cell references. Gord Dibben MS Excel MVP On Wed, 26 Apr 2006 08:42...

2003: Referencing Object On Subreport
I've Googled this and *think* I'm doing the right thing, but no-go. Report A has subreport object "subViolations" whose SourceObject is another report. Within Report A's code I am trying to change the caption on a label in the subreport. Generically, it seems like this should work in the Immediate window: Me.subViolations.Report!lblReportHeader = "Compliant" or, simpler still: ?Me.subViolations.Report.Name But both throw Run-time error '2455'; "You entered an expression that has an invalid reference to the property For...

SQL Statement #2
Anyone know the SQL statement(s) to delete items from the database that haven't been sold since a certain date? Are you sure you don't want to just make inactive if it hasn't been sold by a certain date? This would be the much preferred method. Otherwise you will lose history data in your reports. "Krista" <Krista@discussions.microsoft.com> wrote in message news:868A839B-96B1-4F11-BC49-ECF256BE7172@microsoft.com... > Anyone know the SQL statement(s) to delete items from the database that > haven't been sold since a certain date? On Jun 5, 7:19 a...

Referencing worksheet name in formula
I am trying to find an elegant way to refer a worksheet name in an easily copied formula. In this particular example, I have a sequence of month-specific worksheets, eleven of which reference the month prior to bring up YTD figures. I've just become acquainted with the INDIRECT function but that seems a bit cumbersome, as it entails either modifying the cell reference portion of the formula (e.g., =INDIRECT($A$37&"A16")+D16, where $A$37 references the previous month) or requires a reference cell on each sheet for each desired referenced field. I'd like to find a reference...

Need Help with an IF statement
IFF([CSAandSELinventory]![CountOfSERIAL_NUMBER]=[CSAandSELinventory]![LOWLIMIT],"YELLOW") IIF([CSAandSELinventory]![CountOfSERIAL_NUMBER]>[CSAandSELinventory]![LOWLIMIT],"GREEN") IFF([CSAandSELinventory]![CountOfSERIAL_NUMBER]<[CSAandSELinventory]![LOWLIMIT],"RED") I am trying to build a pie chart report on a form. I am using the above to assign colors based on my inventory levels. I know this will not fill the pie chart with color, but it gives me the information I want. Any help would be appreciated. I worked it down to this: COLOR: IIf(...

iif statement 10-10-07
Im back again, if anyone can help I have 2 fields that when the result is seleceted as below Level 1 pass (First field) and level 1 pass (2nd field) I want the result "Full award" or Null I have tried the following statement below Result: IIf(([aon portfolio result]="Level 1 pass" And [aon exam result]="Level 1 pass"),"Full Award",Null) It will run the query but only after acting as a parameter query, but will not show the result full award ehwn the criteria is met Help Me again I have kind of solved problem? The 2 fields are both look ups,...

If Statement #3
I have a formula in a spreadsheet at work which requires more than seven nested if statements to give me a true answer, is there a way for excel to do this or is there some other function which could be used instead of if. This is a problem i have had for a while so any suggestions would be appreciated. Patrick ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Patrick, There are many options, for instance this recent reply and responses shows a couple IF(G13=0,IF(I1...

Help with an IF statement please...
Hello all... I have a column with these values (A+, A, A-, B+, B.....) and I am trying to assign numerical values to each grade I thought I had the formula down but I keep getting error messages... can you please tell me what I did wrong... Here is what I tried to enter... =if((if(D143="A+","100",IF(D143="A","95",IF(D143="A-","90",IF(D143="B +","89",IF(D143="B","85",IF(D143="B-","80",IF(D143="C +","79",IF(D143="C","75",IF(...

Nested IF Statements
How many nested IF statements can I have? I have 20 possible numeric values which can be in cell B1. These numeric values represent a part description. I want to be able to automatically populate cell B2 with the description of the numeric value in cell B1. This works when I limit the number of IF statements to 7, however, I need 20. Is there any other way to do this? Hi Tony, Yes 7 is the limit for nesting functions. Generally one uses a lookup (VLOOKUP or HLOOKUP) in cases like yours. Read Help and return with questions if needed. Chip has some suggestions at www.cpearson.com ...

If-statement (deal with date and time)
Hi all, What formula should I input into the cell if I want to check th current date and time against a given date and time, i.e. If current date and time equals a month (say, June) then display a formula (say, = A1 + B2) else 0 I have wrote such code: = IF(MONTH(NOW()) = -a month- , -formula- , 0) but it does not work. I am using xl2000. Thanks -- Message posted from http://www.ExcelForum.com The 'month()' function returns a number from 1-12, so make sure you if() function checks for a number, not the name of the month. Alternatively, you can define all your months as number...

Microsoft Money 2005 shows its true intent / Check Statements!!
Okay, I've had on and off major problems with MS Money 2005 and as a software company manager I know my share of buggy programs and can work around them pretty fairly. Knowing that Money is a big product with lots of features for advanced and newbies, it is a very good attempt at trying to address these two divergent issues with the same code. But I came upon something serious and is a potential class action lawsuit waiting to happen if others have this same issue when they get their credit card statements. A few years ago, I signed up with bCentral for a service that they provide...

IIf statement in query, Null value change to "0"
I have two fileds (OT and DT) that I am trying to add together in a third field in a query. Access will not let me do so if one of the values is null. Therefore, I am trying to change the value to "0" if the field is null. I have come up with the following two expressions: IIf(IsNull([OT]),"0",[OT]) and IIf(IsNull([DT]),"0",[DT]) However when I use the espressions in criteria, the null values do not change to "0". Any suggestions? Is there a way to get the values of the two fields added together without changing the null fields to...