Formula's Keep Disappearing Formula Does The Calculation And Then Disappears
I have a very large spreadsheet which holds a lot of data, and has a custom reports system built into it, (i.e. running on a load of macro's)... The reports gather their info from a range of hidden cells which run different formula's to provide such results as 1 or 0 so that it collates into another sheet...
however, I have found recently that when I need to update any of the formula's and whatnot, the formula does the calculation and then disappears... whereas I need the formula to be there constantly so that the report is providing the correct information!
one such formula is:
=IF(AND(AC11"No Calc",AC11>=1,AC11
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Macrocreated Formula Disappears Without Pattern
On a sheet, I have a Worksheet_change sub that checks if the cell changed is in column 1, 10, or 19 and if it is, then the six cells to its right are filled with formulas I need. All formulas work, except for one. I've checked over and over again and the formula itself works fine  the coding behind it is exactly what it's supposed to be. But for some reason sometimes the formula isn't put into the cell. I haven't found any pattern yet with this problem. Even stranger is that the formula is identical to one in another column before (which is also entered by the macro) and the other one has never disappeared yet. I'm setting both formulas with the abc.FormulaR1C1 property (I don't use the abc.Formula property to avoid having to write three different formulas  one for each possible column change).
View Replies!
View Related
Formula For Deleting A Formula When The Formula Has Done Its Calculation
I was wondering if there is a formla that will delete a formula when it has done its calculation, or stop the formula from constantly updating. I've got a formula =IF(COUNTA(A1)=1,TODAY(),"") When something is typed in Cell A1 the cell with the formula will input todays date. Is there a formula that will stop this formula from updating, as when you go into the file on a different day the date would have been updated.
View Replies!
View Related
Calculation Of Time Formula
I am trying to calculate the response time between when a phone call comes into my workplace and when the responder calls back...I have created a formula that does this using the times and dates of when the calls were recieved and went back out. This works except when the call comes in on one day and goes back later in the day the next day, making the response time larger than 24 hours. I also have it set up to eliminate 15 and a half hours from the calculation because our place of business is not open during this time. Further details.... The formula currently being used is... =IF(G50=E50, H50F50+(H50
View Replies!
View Related
Date Calculation Formula
is a formula to work out how many paid sick days an employee is entitled to based on their employment start date Up to 6 months service the entitlement is 5 days After 6 months it is 20 After 1 year 40 After 2 years 60 After 3 years 80
View Replies!
View Related
Formula: Choose The Calculation
I am putting together a formula that will be able to choose the calculation. There is a couple of choices with the spreadsheet registry, non registry, and interfile. They have different standards per hour 56, 40 and so on. What I have so far is not working. It is =volume/(time*standard),Volume/(time*standard) with each standard being different to bring the correct percentage. How can I put the two to three formulas in one cell
View Replies!
View Related
Step Calculation Formula
I have a simple calculation  Say 06. What i need to do is, if the value is <7 place a 10 in the cell, or if the the value is <12 place a 5 in the cell, or, lastly if the value is <21.5 place a 0 in the cell. Is this at all possible  as i can only do it with conditional formatting using colours at present
View Replies!
View Related
Need Assistance With Average Calculation Formula
I get a "#value" error message when I utilize the formula noted below. I seperated it into distinct sections so that it is easier to view. Basically, what the formula is doing is determining whether if two values are the same, then take the absolute difference of the average of other values ,excluding one of the previously noted values, and compare that difference to a different parameter. If the difference does not exceed the parameter, then calculate the difference; otherwise the ending result is zero. Pls. note that the error seems to occur when I input the fifth section into the formula. =IF(K23="","",IF(AND(K23=E23,ABS(B23AVERAGE(F23:J23))>B20),B23ABS(AVERAGE(F23:J23)B20), IF(AND(K23=F23,ABS(B23AVERAGE(E23,G23,H23,I23,J23))>B20),B23ABS(AVERAGE(E23,G23,H23,I23,J23)B20), IF(AND(K23=G23,ABS(B23AVERAGE(E23,F23,H23,I23,J23))>B20),B23ABS(AVERAGE(E23,F23,H23,I23,J23)B20), IF(AND(K23=H23,ABS(B23AVERAGE(E23,F23,G23,I23,J23))>B20),B23ABS(AVERAGE(E23,F23,G23,I23,J23)B20), IF(AND(K23=I23,ABS(B23AVERAGE(E23,F23,G23,H23,J23))>B20),B23ABS(AVERAGE(E23,F23,G23,H23,J23)B20), IF(AND(K23=J23,ABS(B23AVERAGE(E23:I23))>B20),B23ABS(AVERAGE(E23:I23)B20),0))))))))
View Replies!
View Related
Formula/Function For Commission Calculation
I put in excel an employees gross fees for a month,, their commission calculation is based on the following scheudule, for which i'd love an easy calculation, function, code etc. for.. $0  $10,000  60% commission $10,001  $15,000  65% commission $15,001+  70% commission.. i'm sure this seems simple, but i just can't get it because if for instance their first gross fee is $12,000, i don't know how to have it calculate the first $10,000 at 60% and the last $2,000 at 65%. any help is greatly appreciated.. ps.. my excel sheet is set up as follows: Rows ag (stuff that is irrelivant) row h, gross fees row i, commission (in dollars)
View Replies!
View Related
No Calculation Flag, And Percent Formula
1. In neighborhoods that have zero units in a given price range I have it to display "" , because this unit is not actually zero, the data is not available. Therefore a #VALUE! is displayed for the percent because it cannot calculate the "". How do I get excel to glance over "" and flag it for no calculation? 2. For the percentages I am having to manually do them row by row. I would like to set it up in a manner that allows me to copy the formula down by column and across by row correctly. For instance in the percent for Mira Lagos I have =B4/N3 where b4 is the units for mira lagos and n3 is the total. I can drag that formula across by rowto get all the correct percentages for mira lagos price ranges only, but I cannot copy this formula down by column to any of the other neighborhoods. In otherwords I have to do a new formula for each subdivision. e.g. Grand Peninsula=B5/N3 Meadow Glen(Mansfield)=B6/N3 ...etc Again I would like to make it so I can copy the formula across by row and down by column so excel will automatically compute it.
View Replies!
View Related
Declining Balance Calculation Formula
I need to locate/write a formula that can calculate the declining balance on revolving interest loans, such as a credit card. The formula needs to calculate the number of remaining monthly payments based on a stated interest rate and payment amount (i.e., present balance $2000, annual interest rate 18.99%, payment $60 per month). And then, if possible, the formula also needs to translate the answer into a definite Month and Year going forward from today's date (or calculated in another cell of the worksheet for display). In essence the formula would begin as (2000 + (2000 * (.1899/365)) * 30)  60 = then repeat the calculation using the answer above, and so on, until the original principal amount had declined to $0, and finally count the number of months it do to get there. This is essentially a mortgage type of calculation. I know I could write the formula repeatdly one month at a time across a few hundred/thousand cells, but there must be a more economical way to do so.
View Replies!
View Related
Rank Calculation (with Correction) In Array Formula
I'm trying to calculate the sum of rank vlaues in an array formula (required for a MannWhitney Utest calc). For example, I have the results of a survey quesiton (15 rating) with particpant groups of Sales, Marketing & Other. I want to sum the Ranks of the data points that come from Sales or Marketing (but not Other). The added complexity is in the need to add in the Rank correction value to account for ties. The conditional arrays are the tricky part. I'm very close, but the array formula is still including the Other values. If I delete those data points the formula works great. For those with strong stomachs, I've copied my latest formula below. $C$% = "Sales" and $F$5 = "Marketing"... ={SUM(IF('Survey Data'!$D$3:$D$30=$C$5,RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30))+(($M115+$N115)+1RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30),0)RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30),1))/2))}
View Replies!
View Related
Formula To Count 12 Months Back For Finance Calculation
I have a spreadsheet that each month, we populate a new row of data. The rows are already set up in the spreadsheet, but we just populate the new row. We are calculating a rolling 12 month total. Each month, we have to modify the formula below to pick up the last 12 months. For example, next month we will populate data into cell M91, then we need to manually modify our formula to read M80:M91. Wondering if there is a way to have the formula below to look at a range, such as M100:M1, and count the last 12 months? This would eliminate us having to change this each month on several spreadsheets. In Summary: I would like to replace the M79:M90 to count the last 12 months instead of changing the formula each month. Here is the formula: =(FVSCHEDULE(1,M79:M90/100)1)*100
View Replies!
View Related
Formula Structure: The Total Fees (H2) Is A Part Of The Calculation
I am attempting to calculate commission (J2) based on the data entered in cell D2 1. The total fees (H2) is a part of the calculation. It represents a value from .5% to a maximum of 3%. 2. If the Loan Description is specifically ‘80/20’ then 80% of the Loan amount is used in calculating the commission. (note: ‘80/20*’ is also a valid entry). Otherwise the total loan amount is used. D E F G H I J Loan Dsc. Int. DSCNT% YSP% Fees Loan Amount Commission 80/20 7.52.0 1.0 3.0 $137,403 $4,122.09 I attempted this formula and obviously it’s incorrect: Calculating Commission (J2) =IF(H2=" "," ",=IF(D2="80/20",(I2*0.80)*(H2*0.01),I2 * (H2*0.01)) 1. If H2 is blank then TRUE enter a blank 2. FALSE: H2 contains a fee rate then calculate the commission Commission Calculation: If the Loan Description is 80/20 then take 80% of the loan amount and multiply it by the rate fee amount (as a percentage) to get the commission. If the Loan Description is NOT 80/20 then use the whole loan amount in the calculation.
View Replies!
View Related
Formula Not Updating: Using Tools, Options, Calculation, Automatic
I have a table with rows that keep growing. But I have place formula in the whole of column F, i.e. F2:F66565. When I import information from MS Access into column A to E, the formula in F does not work, until I copy from F2 to the end manually. I have tried using Tools, Options, Calculation, Automatic. That doesn't work, I have also tried F9, that doesn't work and I have also tried checking Precision as Displayed under the calculation tax in ToolsOptions, that doesn't work either.
View Replies!
View Related
Formula Calculation To Be 1 Of 2 Values & Increment Cell Based On Result
I'm quite a novice at Excel. I have a column of values that I sum as follows; A 0 0 0 0 0 0 <sum of A1:A5 A formula may change one of the values in the above column to a '1' which means the sum will become '1'. The sum can only be '1' or '0' and only one value in the column will ever change. I need to add a value of 2 to another cell (say, C1) when the sum of A1:A5 changes from a value of '0' to '1'. I know this will probably involve the worksheet change event but am having a problem implementing it.
View Replies!
View Related
IF Formula: Check If “F13” Cell Is "+" If Not Do Calculation (F13E13) And Stop
I have a IF formula that check if “F13” cell is "+" if not do calculation (F13E13) and stop, if yes go to the previous line and check if the “F12” is "+" if not do calculation (F12E12) and stop, if yes continuo to the previous cell and so on until you find cell without “+” The EXCEL IF formula is =IF(F13="+",IF(F12="+",IF(F11="+",IF(F10="+",IF(F9="+",IF(F8="+",E8F8),F9E9),F10E10),F11E11),F12E12),F13E13). I'm looking for VBA code with FOR to run and do the same without the limitation of 7 if inside if.
View Replies!
View Related
Sheets Keep Disappearing
I have a shared file in excel and it is used by up to ten users, lately sheets keep disappearing and (of coarse) no one knows anything about it. Does anyone know how this is happening? Or what security protocol I can use to keep it from happening?
View Replies!
View Related
Disappearing Incell Instructions
I'd like to have a cell contain instructions that disappear when text is entered into the cell, much like the "help" box on the upper right of the Excel window has a label that says "Please type question here", which disappears when you actually type in a question.
View Replies!
View Related
Disappearing Addin List
I have workbook which automatacally 'installs' an addin, i.e. ticks the box. When I open the workbook without Excel running, the code fails to install the addin. When I then use Tools > Addins, the panel of addins is completely blank. I can only restore the addins list by closing Excel and reopening it.
View Replies!
View Related
Disappearing Data Validation Lists
I have a very large spreadsheet that I work with on a daily basis. The main portion of the spreadsheet is broken down into weekly sections (for example, columns GF thru GM contain data sections for one week, which then repeats with the next set of columns, etc.). Below these weekly sections are detail and summary sections for all the data entered above. In the main weekly portion, each week contains a size dropdown list which calls to size information in the detail portion below. Recently, the data validation lists have started disappearing. By disappearing, I mean that the data validation for the cell no longer exists on a large portion of the list cells. I have tried recreating all of the data validations, saving the file, and reopening it, and when I do, they've disappeared again. I have no idea what to do at this point. The functionality of the spreadsheet is crippled without the sizing dropdown lists (for various lookup reasons).
View Replies!
View Related
Custom Right Click Buttons Disappearing
Have a spreadsheet wherein I have added some custom controls to the rightclick function of the mouse. The buttons and their associated code runs great for all but one user in my office. For that user, the custom buttons do not even appear on his rightclick menu. I have looked through his settings to make sure that his security settings are the same as everyone else that uses the spreadsheet, and they are. All of the other modules in the spreadsheet work fine. Is there some other setting that would control the display of his rightclick buttons. Perhaps a windows security setting that I am missing? The following is a snippet of the code that adds one of the rightclick buttons for example. This code is stored in the 'Workbook_SheetBeforeRightClick' event. With Application. CommandBars(" cell").Controls _ .Add(Type:=msoControlButton, Before:=1, temporary:=True) .Caption = "Create Panel Hyperlink" .OnAction = "CreateHyperlink" .Tag = "brccm" .FaceId = 2169 End With
View Replies!
View Related
Disappearing Data Validation Dropdown
All of a sudden, the data vaildation dropdown boxes on a specific worksheet have disappeared. The list reference is still correct and the data validation dropdowns work just fine on other sheets in the same workbook. (BTWI'm using Excel 2003 and the panes are not frozen).
View Replies!
View Related
Result Arrives And Disappears
I begin in the Word of VBA. It's the first time I've a formula which seems necessitate VBA to be written down. Before to try to execute my complicate macro, I've begun with simple tricks. But I've got a strange comportment of Excel : I've the following Sub :
View Replies!
View Related
Scroll Bar Disappears Clicking It
I have a chart that I just added some dynamic text boxes to. That is the text is linked to a cell so that the text is automatically updated. I also have a scroll bar that is used to adjust one of the cells. However, now once I click on the scroll bar it just disappears. I can click on the space where it is located and it will appear for a split second. Also, if I scroll down in the worksheet and then back up over the scroll bar the image will be refreshed and I can see the scroll bar again. I didn’t have this problem until I added the dynamic text boxes, it worked fine with static text boxes. Does anyone have any idea how to get around this, or do I have to stick with the static text boxes?
View Replies!
View Related
Userform Disappears After Clicking No
i got the userform to popup when the workbook is open and the buttons work fine... only problem is when i click on a button, it will ask the user yes or no... if the user press yes, it performs the required macro without any problem... but when the user press no, not only it will close the question, but also closes the userform... I would like the 'no' to close the question but not the userform...
View Replies!
View Related
VBA: Popup Calendar Disappears When Script Stops
My Workbook contains the following macro in Sheet 1, which displays a popup calendar in L15 when that cell is selected: Private Sub Calendar1_Click() ActiveCell.Value = CDbl(Calendar1.Value) ActiveCell.Select Calendar1.Visible = False End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Application.Intersect(Range("L15"), Target) Is Nothing Then Calendar1.Left = Target.Left + Target.Width  Calendar1.Width Calendar1.Top = Target.Top + Target.Height Calendar1.Visible = True ' select Today's date in the Calendar Calendar1.Value = Date ElseIf Calendar1.Visible Then Calendar1.Visible = False End If End Sub.........
View Replies!
View Related
Text Disappears When Word Wrap Is Used
Whenever word wrap is applied the text is not visable in the cell. It remains visable only in the bar above. I have already confirmed autofit and it reduces the column to 1 character wide. I tried detect and repair on the installation and nothing. I even upgraded from Excel 2k to Excel 2003 and no change.
View Replies!
View Related
Inserted Picture Disappears After Losing Focus
I have the same problem as Paul C in the following post: (Pictures disappearing) "Hi not a VBA problem, but when i insert pictures or logos into my workbook they disappear, i can insert a picture close the workbook but when i reopen it the picture has gone, i have to click on where it should be to get the border to appear, the move it to get it to appear. I have tried inserting BPMS, JPGS, GIFS, but i have the same problem with them all, I have used the bring forward command but still have the same problem, I have even tried reinstalling Office 2003 and installing all the latest updates but still get the same problem, any ideas?" I have tried:  Tools > Options > View > Objects > Show All  Changing macro security level  disabling macro security in my antivirus
View Replies!
View Related
Locals Watch Disappears; Program Crash
I'm in the process of writing/editing a macro, but after I run the macro once, the Expressions in the Locals Watch window disappear, and if I try to run it again it produces an "Excel has encountered an error and needs to close". The macro probably doesn't make much sense outofcontext, but perhaps there's a glaring error that's causing this. A guess  possibly related to the Error Handling in the vba? (1st time I've used this in a macro). Option Explicit Sub Compare() Dim strNaspK As String Dim strCtryK As String Dim intCtryRev As Integer Do Until ActiveCell. Offset(1, 0) = "" ActiveCell.Offset(1, 0).Select strNaspK = ActiveCell strCtryK = ActiveCell.Offset(0, 2) On Error Goto ErrJump:........................ Basically, I've got two sets of data on different sheets, with NASP ID and country being variables. Just trying to import the data from the 'Comparison' sheet, if both the NASP id and country matches that on the first sheet, established by the variables.
View Replies!
View Related
Replace Character Used In Formula Where Formula References Cell
I am using vlookup to get a cell value from another sheet, but if the cell has "&" or "/" I need to substitue "&" and "/" with "and" so that the cell can be added later to a url. i am using =VLOOKUP(a1,Sheet2!A1:W17968,6) to get the value of a1 in sheet 2 and return the value of column 6 this will return "Audio Cables & Leads" but i need it to say Audio Cables and Leads I need the formula to also check and replace "/" with "and" as well so cables/wire will be Cables and wire
View Replies!
View Related
Combine Formula Which Reduce The No. Of Additoanl Formula's Columns
I have some student marks in columns B to F. If some one get below 40 marks he fails and the subject in which he has been failed will come in Remarks column. If he has passed in all subjects remarks column will show Pass. To bring failed subject initial in Remarks i have to create 5 additional column and then thru Concatenate i joined them. Is There any formula to show fail subject in Remarks column without creating 5 additional columns?
View Replies!
View Related
Copy A Formula In A Cell And Then Paste Only The Text Of The Formula
I would like to copy a formula in a cell and then paste only the text of the formula, but I can't figure it out. Basically, I would like to avoid going into the cells and absolute referencing or hitting F2, then copying the text. When I hit "Ctrl C" to copy the cell, then hit "Alt/E/S/F/Enter" to paste the formula, it is just like a regular copy/paste formulawise in that the references move.
View Replies!
View Related
Add Addition If Condition To Existing Formula: Long Formula
This task joins a string together based on a number of characters per cell in the range. I want to isolate one range, Col N, and add an IF condition to it. There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....
View Replies!
View Related
Link Formula / Update Formula Automatically
I’m working on quite huge spreadsheets. The same formula will be used on more than 30 sheets. So far if I have to change the formula in one cell I have to go to each sheet and change it manually (which is quite boring and time consuming). I’m wondering if it’s possible to reference all the formulas on the sheet to one sheet (which some kind of a master sheet with all the formulas). I can copy the formula with the “Past Special” function but the function is not updating if I change the formula on the main sheet. So is it possible to link it and that it’ll be updated automatically when I’m changing the formula on the main sheet?
View Replies!
View Related
