I am working in an excel file with many sheets. I have vlookups on several pages that refer to a different page in my file. However, I am finding that the vlookups are not autocalculating. They are fixed on a value until I click into the cell and hit 'enter'
View 9 RepliesI'm trying to convert the values entered by users into specific cells into a specific percentage. When I use this macro in Excel 2010, I can enter the value to be calculated directly into the cell and the macro runs automatically. When I open the file in Excel 2007, the macro does not calculate properly unless the value to be calculated is entered into the formula bar.
For example, in Excel 2007, if I enter '30' in cell E11, it should come out as '20%'. Instead it comes out as '0.2%' unless I enter '30' into the formula bar, which then calculates properly. It is far more efficient to be able to enter the value directly into the cell.
[Code] .....
I've a lot of formulas (made in vba) in a Sheet. The problem is that even if the formulas all work as they should from the beginning, somtimes when I'm running some other macros, the formulas instead shows some error message. When selecting the cells and pressing enter, the formulas once again shows the right answer. Since it's extremely inefficient to select and press enter at each cell, what I need is some way to update these cells. Anyone know a quick way to update all formulas in the sheet?
View 6 Replies View RelatedI need to calculate the amount of time my daughter will be in daycare which varies each week. I have to give the same sheet that I have hidden calculations on to the center. I have to have the time she arrives at 7:15 AM (Cell A1) however they don't start to bill until 7:45 AM (Cell A2) and continues until 11:30 AM (Cell B1) and need C1 to be total hours between A2 and B1
I need a formula that will calculate the hours between 7:45 AM to 11:30 ONLY if cell A1 has a time entered in it. If A1 is blank then it should calculate 0 hours.
Example (WITH something entered in A1)
Arrive Time End Billable Time Total Billable Time
A1 - 7:15 am to B1 - 11:30 am C1 - 3.75 Hours
Start Billable Time
A2 7:45 am
Example (WITHOUT something entered in A1)
Arrive Time End Billable Time Total Billable Time
A1 - to B1 - 11:30 am C1 - 0.00 Hours
Start Billable Time
A2 7:45 am
If it has to be in 24 hour time is there a formula that I can enter in another cell to convert 12 hour time to 24 hour time again ONLY if A1 has something entered in it?
Should be simple but I can't get this to work,
I have used =value(a1) that prevents any formula, but it also prevents a zero being entered.
I have amended trying and/or statements in the validation but then it allows zeros and formulas!
I have a workbook that is linked to 2 text files and updates just fine for me but when someone else opens it it does not calculate the new information just keeps the old. New text files are ran from our database to excel every night. When the workbook is opened the formulas should recalculate based on the updated info in the text files. Problem is when I open the file it performs perfectly and updates as it is supposed to but when my colleague opens the file it is not updating and does not show the new updates. I have checked to make sure the calculation is set to automatic. I have also confirmed that we are both working with 2003 version. I even had her open the text files and close and reopen the workbook and no go. I hat her do ctrl+alt+f9, even ctrl+alt+shift+f9. Had her click on the function, hit enter and nothing. Even tried find and replace = with = to wake up excel and nada.
View 9 Replies View RelatedI am having a little trouble with IF. My pest control sales people are paid on straight commission. In addition to paying them a sliding percentage of the total contract value I pay them 50% of everything above $100 they charge on the initial service.
calculating these formulas?
Attached is the payroll document.
Col A - budget items (description)
Col B - budget dollars
Col C - actual dollars
Row 10 - summation Cols B and C
When the budget is prepared the actual dollars in Col C are equal to the budget dollars for all items. For instance the formula =b2 is placed in cell C2. As time passes the user records actual dollars in Col C for each item by entering the actual amount directly into the cell. Actual dollars do not become known all at the same time, so that Col C will contain a mixture of formulas and entered numbers.
How can I highlight the cells in Col C that have entered numbers rather than formulas.
When i copy the formulas below in an excell sheet i have made the references absolute, but i want the sheet name to change as i move through each sheet in the workbook (if it is greater than sheet3)
The column of formulas needs to insert a new column and add formulas to it in the next location. Ie (B4:B16 to C4:C16). B4 Being the heading/sheet name.
I have what is probably a simple problem for most, but can't figure out what to do.
In the sample sheet attached, I have times in column E, and an action describing what has happened in column G. What I want to do is calculate the length of time between an opening action and closing one, but don't know how to go about it, as there can be an empty cell(and sometimes more) between each open and close.
Using Microsoft Excel 2007 and all of a sudden, my spreadsheets are not automatically calculating the formulas. It does not matter if I have other workbooks open or not. I still get the problem. It does not happen 100% of the time to make it even more complicated.
- Calulation set to auto in Excel Options.
- No VBA functions being used. I can the worksheet summing 1 + 1 and get the error periodically. It does not have to do with the spreadsheet being too complicated.
- Even if I can hit Ctrl-Alt-F9 to force the formulas to calculate, it won't work.
I am trying to get the formula for calculating yearly rental imcome. The range is 10 years and the interest is 34%. The first year payment is 42,000 and the 10th year payment is 56,000. I can't figure out how to do the other years. The principal is 325,000 and the sale value is 425,000.
View 4 Replies View RelatedI need to dynamically build a formula for calculating a running percentage for a row of data. The columns that are referenced by the formulas are static (f thru r), but the row number can change depending the number of rows of data. I have a set of data that can have one to many lines in it (the data is placed on the worksheet using vba code). After the data is placed, a total line is built using this
'Find row number for total row (2 rows down from last row of data)
endrow = Range("a35").End(xlDown).Row
sumrow = endrow + 2
'Set sum formula for the "e" column
Range("e" & sumrow).Formula = "=sum(e35:e" & endrow & ")"
'copy total formulas to columns f thru r...............................
i'm trying to get data added in one sheet of a workbook to automatically be entered into another sheet. such as a monthly, Quarterly and Annual balance sheet.
View 3 Replies View RelatedI am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.
View 9 Replies View RelatedI'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.
I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies View RelatedI have a macro running this code to strip out unwanted formulas and formatting.
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False
Range("CDandC").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2
'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If
Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value .........................
A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.
Is it possible to hide formulas from the formula bar while still having the formulas active?
View 8 Replies View RelatedI have a spreadhseet with various functions on it and what I am trying to do is this.
Cell E4 returns a >35 or <35 true or false value
Cell G4 is either blank or has "Yes" text type into it.
What I am trying to do is get cell F4 to return certain arguments.
E4 = >35 and G4 is blank I want it to state "Email Hiring Manager"
E4 = ,35 and G4 is blank I want it to state "Wait"
I have a basic IF formula that returns this
=IF(E4>35,"Email Hiring Manager","Wait")
Then if cell G4 is populated with a Yes the formula needs to overwirte the origonal if with the return arguments of
=IF(G4="Yes","Email Agency","Email Hiring Manager")
If yes then what would be Email Hiring Manager (yes will only be input if E4 is greater than 35) will be overwritten with "Email Agency"
Can this be done with two If formulas or does there need to be 3 or more to count if other IF formulas are actually returning a value?
If you have a cell with the value ="2*c2+3" NB: (Notice the ""), then to make excel convert the formula in another cell to =2*c2+3 (notice the removal of ""), so that it can calculate the value of the cell instead of showing a textstring?
I have formulas in a column and they are working unless I edit them to include another function, more cells, whatever, then they display as formulas instead of the result. I've gone to Tools --> Options --> View and the Formulas box is not checked. As well automatic calculation is on not manual.
View 4 Replies View RelatedIm trying to enter an employees name into a data list. I have a program that generates a list including their employee number into column B. I want to write something to put the employee name into column C. Im working with 60+ employees so i dont think if statements will work? anyway to reference a chart or table with the numbers and names on it?
View 9 Replies View RelatedI want to run a macro when cell b2 is = to “yes” in worksheet called “Charity Helpers”
View 9 Replies View RelatedI am new to excel. I want to use Marquee in excel which takes user inputs from a message box and display those values in the web browser (found some old topics about web browser) untill we click the amend or new button.
View 6 Replies View RelatedI have to enter date into cells. It is being written from the lists of paper, where for date is entered only year (e.g. 1987), but I have to enter it into excel with day and month (the program working with data only recognizes this form; eg 01.01.1987).
I was thinking of writing a conditional formatting, to remind if I will forget to write 01.01. before year.
I prepared formula like this (to check for the dots):
=IF(AND(MID(AD2;3;1)=".";MID(AD2;6;1)=".");1;0)
But it is not working, because the function recognizes general formatting of cell instead of date formatting (28491 instead of 01.01.1987).
Fill the corresponding cell with last value entered?
View 9 Replies View RelatedHow do you make an enter space in msgbox? I'd like the layout to be something like this,
Hello My name is Edmund.
His name is Edwin.
Wait, my name is actually Eddy.
I am using the followng code to check data is entered in a cell ...
View 6 Replies View Related