I'm trying to create an array formula that returns all the cities in a given state by imputing the state's abb in cell K2. The returned cities go into column J. California has the most cities(430) and the cities are 5000 rows deep.
I want to set a conditional formatting rule that makes a cell red or green depending on if the value in the cell is larger or smaller than the value in another cell +2% or -3%. If neither of these conditions are met no formatting should be applied..
a) In the cell (R104) where the condition is set I have the following formula: =if(BP104=0;"";BQ104/BP104) (the values in BP104 and BQ104 is retrieved from a database)
b) The conditions I've set in R104 are the following
1) =isblank($R$104) (condition: "nothing")
2) =$R$104<$R$106-0,02 (condition: "red")
3) =$R$104>$R$106+0,03 (condition: "green")
c) In cell R106 the value there is a value of 90%
The conditions seem to work fine when the value in R104 is higher or lower than the conditions set in 2 and 3 above but when the value is blank in R104 the cell goes green, which it shouldn't.
How do i make a formatting rule permanent to certain cell's.
The steps i followed:
I selected the columns and rows to be formatted. in conditional formatting I selected new rule. then selected the second function, "format only cells that contain" in format only cells with: specific Text , then " ending with" Over Issues.
in format i selected the font and fill.
I saved and close the document, after reopening the rule is no longer working.
Im trying to find a formula that when a cell is empty ie this cell is linked to another cell on another worksheet and info has not been entered or is 0 then i want the cell to display the the current date ie =NOW()
I'm playing with an excel file that has 3 different spreadsheets and basically the same layout just different product lines on each sheet. I need to find a snipet of VB code to change cell color depending on if another cell is used(filled in) or if it is blank. I'm very limited to my VB skills but can tinker enough to get what I need if someone points me in the right direction.
here is an example of what I'm trying to accomplish using the following Columns:
In my spreadsheet the user has the option to put in a numerical value into different cells, which will update (add to) another. So if we have an initail value of 20 in "I10" and the user puts in 20 in "W12", then I10 will display 30.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("W12:X24")) Is Nothing Then Select Case Target Case Is = Range("W12") Logbook.Show Range("I10").Value = CInt(Range("I10").Value) + CInt(Target.Value) Case Is = Range("X12") Logbook.Show Range("J10").Value = CInt(Range("J10").Value) + CInt(Target.Value) Case Is = Range("W13") Logbook.Show.................
Is it possible to check the conditional state for a specific condition for a specific cell. For example a cell has 1 or more conditions.
A condition will toggle to a condition if true or false etc. The specifics is i use red (color 255) if a cell has not met a specific condition (of 1 or more conditions) So if the cell is mandatory to be populated, and is blank, it will be red, if not it will be something else.
But i want to loop all visible cells with conditional formatting, and where the cell has a condition when true will show interior color = to 255 return the cell address. To msg the user where data entry is still required etc.
i have an excel spread sheet (2010) that contains city, st and zip in one cell. i need to separate these into 3 individual cells. My problem is the some of the cities are one, two and sometimes 3 words so using a delimiter of space will not separate them correctly.
In my attached sheet I've to find out final result in Column "F" (green shaded). My boss many times a year increases salary by certain percentage (say 30% or 40%) which is given in "E" column. After that I need to adjust it according to Pay Scale given in right side. For example one Executive's salary reaches 19880 after % increase. Now in pay scale we see in executive (row no. 13) it crosses 19800 so his final salary should be next upper value i.e 20600 (if it reached to 20700 after % increase, then his final salary could be 21400). That is final salary should be multiple of designation's increment plus minimum gross salary (Here 800X10=8000+11800=19800, since it exceed by 800, so salary should be 800X11=8800+11800=20600)
So we can either pull data from right side table or we can give formula in "F" column to get the required result.
Does anyone know an efficient formula for the “five-even” rounding rule? Someone asked me about this and gave me these examples:
6.6 must round to this ==> 7 6.5 must round to this ==> 6 5.5 must round to this ==> 6 8.5 must round to this ==> 8 9.5 must round to this ==> 10 66 must round to this ==> 70 65 must round to this ==> 60 55 must round to this ==> 60 85 must round to this ==> 80 95 must round to this ==> 100
I am using excell 2007 and am having a few problems with conditional formatting. I am trying to do the following. i have 6 cells in 6 columns ( c2, d2, e2, f2, g2, h2 ) each with figures 17.14+ Now i am trying to add a formula so when ever i insert a number in the cell below e.g c3 i want the cell c3 to turn green or red depending on the value in the above cell (c2). so in all:
c2 has the value 17.14 if i insert the value 17.14 or less in cell c3 i want the c3 cell to turn green. if i insert the value 17.15 or more i want the cell to turn red. now i know how to do this in the conditional formatting feature but i want to do this will all the cells refering the the cell above. i also do not want to create 6 diffrent conditional rules.
Is there a formula i can use that can be pasted into each cell and the cell number edited?
I have a table where Sales are in Column B and a % calculation is in Column E. I want to highlight the cells in green that are over 40% and that also meet the criteria where the sale in that row is above $100.
In conditional formatting I tried to use =and(b4>100,e4>40%) but it did not do anything. I am also okay with using a nested if statement.
What I need is for in the order start date column, I need a formula to pick out the first year where the value is inserted into the column and then insert the year into the order start date column. I would do this manually, but with thousands of rows a formula would prove to be most beneficial.
I have a project where if the user enters 'R' into cell H17 then cells B19:K19 are to be filled with black color. These cells are divided into three fields: B19:E19, F19:H19, and I19:K19.
When I select these three fields and use the conditional formatting tool the only cells that are black are B19:E19. Even though the rule explicitly shows that cells B19:K19 are to be affected. In order to have all three fields black I have to make three rules (one for each field).
The same behavior happens when I use the conditional formatting tool and select the cells from within the tool's dialog.
How can I get all three of the fields formatted with one rule?
is there any rule for random number to be picked up? Any Rule, Any Formulae, Any Calculation Anything, Any authentic routine which may guess what would be the next random number? for example if first random number is 825587 then what would be the next?
I'm trying to find out the rule for de-duplicating data. I am removing duplicates based on an identification number in a data set of about 6000 records, including the duplicates (some records appear about 4 times). Due to the nature of the data I'm working with, there are only a handful of records that are "true" duplicates, i.e. some of the records appear 4 times but there is a difference in terms of location, etc and some are true duplicates in that there is no difference.
I need to know how Excel removes duplicates - does it only keep the first line that it finds for that identification number? Also, is there a way that I could create a rule for it to keep the record with the highest rate for example?
Is there a way to programatically count the amount of conditional formatting rules in a workbook as i have been given a workbook which performs really slowly and the amount of rules seem to be a lot just wanted to count them
how to adapt the solution so it would work in multiple cells. Since I was told to start a new thread, here it is. I want to format a cell based on its contents, If it has one of three entries, it should be greyed. I then want to use the conditional format across the whole page, but the solution given in the other thread refers to a single cell, how can I do this without that reference?
I want to find the correct formula to find answers using excel for the folliowing:
1. >3Sds 2. 3SDs<>2SDs 3. 2SDs<>1SD 4. <1SDs
This question uses 3 sigma rule (see attached worksheet) and the formula needs to be re-arranged to meet new criteria. So, we need to consider both sides of nornmal distributiion (ND) curve. eg. when we say >3SD it means it should count all valuee falling in the rage "Mean(Average)+3SD AND Mean-3SD" i.e two extreme ends of the ND cureve. I have inserted a normal distribution diagram and colour coded the ranges so that it will be easy to identify. Please refer comments inserted in K7to N7, cells that contain formula that need to be reset.
For the first grouping of data (Ex 1) I need to do the following; if the value in column J is less than 49 then delete the data in the corresponding row from column E to K. For the second grouping (Flx 1) if the value in column R is less than 49 then delete the data in the corresponding row from column M to S. I would like to be able to do this for all groups all the way to group Flx 5. All my sheets are set out in this way but I am not sure if I can do this using a formula or a macro (which I am not very experienced at writing).
Is there a way to apply a custom validation rule to a drop down field in Excel (which itself is already using a validation rule)? Here is what I wish to do: I have a range of cells that can only pick up values from a list. I want to further apply a custom validation rule to these cells - display an error message when one chooses an element from the list that has already been used by another cell in the range.