i have 4 areas north east south and west, what is the forumla to enter into a cell that would automatically add the Salesperson when you know the area
Table is
Date, Area, (FORMULA), Amount
13/11/09, North, ( ), Amount
Basically there is a table with the salesperson per area
if i was writing it i would say its as follows
If Area = North then Mr Smith else if Area - South then Mr Jones etc etc
i have all the states listed in A1 and i would like to seperate them into east and west.
example if a1 have FL and want a52 to say East and if i put CA it would say West.
I'm attempting to calculate the bearing of a vector linking two sets of Easting/Northing coordinates and while I've found no shortage of equations online that should provide an answer, none seem to work in my excel sheet. The data comes from the southeastern hemisphere (i.e. Outback Australia) and my answer is needed in degrees to true north.
View 6 Replies View RelatedI have two dates lets call them cells A1 and A2. I then have another cell that caluclates the difference in these dates into a simple number (ie not another date) - A3. I need one of two things.
Ideally I want the whole cell (A3) to appear as a Green (fill) cell if the value is below 5, appear Orange if beween 6 and 9 and appear as red if its 10 or over.
I do not know if you can colour a cell that has a formula in already. So what follows is my current 'work around', which, if you can not colour the A3 cell, then I would need this cell coloured (A4). =IF(D12>7,"Urgent Attention",IF(D12<5,"New",""))
I have a worksheet with pricing & part numbers on sheet1. I put a formula on sheet2 that puts the lowest price in colum C of sheet2. Now I need to also get the part number from the cell before. So if the lowest price is in cell E3 I need to also get the part number in D3, and put it in B3.
I basically want for it to be able to calculate all the games a each team has won in the example below.
View 13 Replies View RelatedIn 1 cell i need a forumla to give me a starting number and take one away each time a code or codes are dislayed in a cell range. Something like this
A1 = 23 days or any number days i needed
Cell range B1:C52
every time a selected 1 or 2 letter code appears in the cell range i want A1 to subtract 1 day from the starting number, i would need it to subtract half a day if one code appears ie HD, the codes may be P, OT, HD
I need a formula showing that if a number is less than say 1.25 then it rounds down to 1.0 and if its between 1.25 and 1.75 then it rounds to 1.5 and then if greater than 1.75 then it rounds up to 2. I need it to work for all numbers not just 1.
View 10 Replies View RelatedI have a worksheet I use at work that calculates volume proccesed hourly, and also shows a running total as you enter hourly figures into the table, my question is can you hide everything in the result cell until the figures for each hour are entered ? currently all the result cells have irrelevant numbers in them until you enter the hours figure and the formulas do there thing, I could relly do with them being blank until the relevant hours information is input?
View 2 Replies View RelatedI am trying to do is use a combination of IF and SEARCH forumlas to return a result based on a choice of constants available to the user. Issue 1: IF Function to determine which worksheet to SEARCH What I would like to add to this is if one of the 3rd criteria (Amount) is under a certain number then the results are derived from the 'Staff' worksheet'. (This is stage I have gotten to so far). However, if the criteria is above a certain number then the results are derived from the 'Executive' worksheet. This is the part I'm so far unable to do.
Issue 2: SEARCH function returning the column per the user's selection on 'Matrix' page In addition, criteria 2 (Type) can be selected which corresponds to a column in the Staff and Executive worksheets. So far I have been unable to get the correct search results to post in the Matrix page. What I mean by this is that currently it is set to Type1 only. I have tried using an IF function in cell G8 and include this in the formula, however, the forumla returns the contents of G8 in the cell instead.
Issue 3: SEARCH results are post in an accending order. At this stage I have only been able to work how how to do this by using an AUto Filter on the Staff and Executive pages and sorting accending that way. Going forward as updates are made to the staff and executive pages I would prefer that this manual task was not required. Instead, the formula used returns the search in accending order.
If you Have a list of account numbers and amounts, is there a way in one forumla to get the average per account?
View 9 Replies View RelatedBasically I have cells like this.
.....
I want to fill this formula down so that the summing occurs all the way down the collumn. However when I fill it down, it just copys the first two forumlas, rather than make the formula correspond to the row.
I have a sheet which calculates payment amounts.
Column titles:
Hours | Rate of Pay | Total
In the hours column usually the entries consist of numbers and everything works fine. However when an employee is on holiday they are still paid.
What I want to do is be able to enter the letter "H" for one of the entries in the hours column. The sheet to translate this as 2 hours.
H=2 x rate of pay = total
I cannot for the life of me get the correct formula to in order to achieve this. I don't particularly want to use a macro for this and others have suggested the "COUNTIF" function.
I have the margin and I have the sale price. How do I figure out the cost?
View 12 Replies View RelatedI have a workbook that uses several indentification fields one for Job ID, One as a subID which occurs for every part of a job, i am currently using formula
View 5 Replies View RelatedIf I minus H is less that 90 count as 1. But if I minus H is greater than 90 count as one but highlight the row on the speadsheet in yellow.
What I am doing is I have a list of about 3000 entrys and I need to determine if (I minus H is greater or less than 90). I then need to count how many times out of the 3000 entrys each entry is greater or less than 90 and highlight the entrys in yellow that are greater than 90.
It would be ideal to determine the difference between I minus H and then categorize the findings of the 3000 entrys to see how many were say 59 and how many were 47 and how many were 92 etc.
I have never seen a forumla highlight a entry based on count if. I am currently rapidly searching the forums here but to no avail.
In cell b2 I have a formula =text(today(),"yyyy") which daisplays todays date as a year, in cell b3 I have =text(today(),"mmmm") which displays todays date current month.
I have a column of Months and Years
January 2014
February 2014 etc
I need todays (current) month and year to indicate in a helper column next to the relevent month and year as a number 1 to use as a reference to return date from the row where the 1 is displayed, I have tried =if(and(b2=a10,b3=b10),1,"")) and it dosent like the fact that the year in the colmuns is entered as standard text, is there any whay to do this?
I have set up a spreadsheet to calculate time as followed:
A1 - Start Time
B1 - Finish Time
A2 - Hours Worked:
=IF(B1<A1,B1+1,B1)-A1
C1 - Start Time
B1 - Finish Time
C2 - Hours Worked:
=IF(D1<C1,D1+1,D1)-C1
and so on.........
I am trying to write a vba code to change the zero values of the sheet to either 1, -1, or 0 according to certain rules. my sheet looks like this:
chcount=10chcount=10
0000
0000
0000
0000
1111
0000
1111
0000
0000
0000
0000
18111
0000
-1-1-1-1
0000
0000
0000
0000
-16-1-1-1
Basically I need the macro to select clusters of zeros in each of the columns with a label of 10 characters (this is an arbitrary number I chose when writing the macro), and if the values of the cells immediately above and below the cluster are the same, change the value of the zeros to the value of those cells, otherwise, the value of the custers remain as zero. So far I have the following code.
I've attached a sheet with details. I have references that I want to copy to a different cell but I want the reference to stay fixed AND allow the column to move.
Book1.xlsx
I'm trying to calculate the area of a Freeform shape.
Is this already stored somewhere e.g a shape property?
If not is there a macro available to do this?
I've got a spreadsheet that could have 1 of 3 possible print ranges:
PR1 = B2:K67
PR2 = B2:K132
PR3 = B2:K197
If there is a value greater than "0" in Cell B68, the print area would be PR2, but if there is a value greater than "0" in Cell B133, the print area would be PR3, otherwise it would be PR1.
These print ranges are such that the page break would would be below B67 and B132, but I'm concerned that I may need to set these page breaks in the Macro.
I'm not sure how to use the IF, THEN, ELSE, in the Macro to test the value of the cell and then set the print area.
can shut off the automatic calculation of a particluar worksheet or better yet, certain areas of a workbook, leaving the remainder of the workbook as Automatic?
For example, a workbook with Sheets 1,2,3 and 4. Can Excel 2007 be set for automatic calculations on the entire workbook except for Sheet3?
I have a table that lists staff according to where they are located, all adjusted using drop down lists, problem is that some locations have 5 staff and some up to 40, so when I set up a macro to print the area, those with 5 consultants look very small because the page area is so big. how can I adjust the print area dynamically so it only prints to 1 row below the last line with text?
View 9 Replies View Related'Get all the sheets
'y = 3
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "OM Files"
For J = 2 To Sheets.Count
With Sheets(J).UsedRange
x = Sheets(J).Name
If x "xxx" Then
.Resize(.Rows.Count).Copy _
Sheets(1).Range("A65536").End(xlUp)(3)
End If
End With
Next
Application.DisplayAlerts = False
When would the AREAS() Worksheet function be used? I've never seen it used and I've been Googling around to no avail.
View 9 Replies View RelatedI frequently receive workbooks containing up to 50 sheets and on most of them there is a selected area.
Is it possible to put a bit a code in the worbook when opening to clear all selected areas on all sheets.
I have an Excel sheet that I want to scroll up and down and I want an area in the upper right-hand corner to display static information. This information could change as more input into the sheet is performed, but it always must remain in the upper right-hand corner.
For example, let's assume the sheet has 200 rows to enter in a credit card application. There would be an area in the upper-right that would display information for some of the cells that were typed in. So as you type information, labels would display information from the form. I know that I can program the selection change event to re-position the box and labels, but if I scroll off the screen, the area does not stay there until I click on a cell on the page. I am using XL2003.
I have a userform that has two comboboxs on it. When the user clicks and selects an item in combobox 1 the code will highlight an area of cells that I set before. When the user clicks and selects an item in the combobox2 I need a piece of code that will highlight a second area of cells while still keeping the first areas of cells highlighted.
How I have it set up now if the combobox1.text = "Monday" then the cells B6:H20 are selected. But when the combox2 click event fires and selects another range of cells the first range selected by the combobox1 event is unselected. Is there a way to keep both areas selected?
I really know nothing about vba so here goes. I would like to enter data in a row with 4 cells of info. then hit enter and return to the first cell and move the row down. all four cells must have data entered. and all four must move down. i tried some code as below i found and i modified but it did not work as expected. this moved the row down when returning the cursor to A2. It also should not copy the data style of the top row.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
[Code].....