I have , for example, 3 values (1, 2 and 3) in column D which is 10,000 rows long.
Is there a formula that will colour part of each row (say C1 - F1) in a different colour each time where there is a match to value 1, 2 or 3?
Is there any way I can use conditioning formatting on datapoints in a graph, so that if certain points meet the criteria they will appear with another symbol?
Suppose I would like to change symbol on the datapoints exceeding the value 200 in this graph: Graph.jpg
Can I do that?
I wish to perform different actions depending on whether certain errors are present in a cell and I can't figure out how to do it.
Attempt (within some For loop):
othCell = ActiveCell.Offset(-95 - counter, 0)
If othCell = CVErr(xlErrNum) Then conditioncount = 0
This generates a "type mismatch" error. I also tried othCell.value in place of othCell on the second line; I don't really follow the distinction in usage for "value". I've also attempted this with Error 2036 in place of CVErr(xlErrNum). I don't know much at all about VBA!
I have a spread sheet where there are multiple rows of price quotes from different suppliers.
Some suppliers have quoted, some however have not and there is a "#N/A" or "no bid".
SupplierA
SupplierB
SupplierC
SupplierD
1.3
No Bid
2.3
No Bid
How can I get a conditional format to check each row for the lowest numeric bid, highlight it but ignore the text?? What I have tried so far doesn't work.
=G31=MAX($G$31:$J$31)
This does not seem to work, I don't know how to exclude the text values from the MAX function.
I wish to set 2 different cases (good and normal sales) for a list of data.
During good sales
- fruits can be sold within 4 hours
- Fruits sold off during peak season (Definition of peak season: 2 calendar days before or on public holiday or 10 days before 31st May)
- < 2 fruits per type
Date of public holiday for example: 1 Jan 2014, 18 Apr 2014, 1 May 2014
For normal sales
- fruits only able to sell of the next day
- Sell of on non-peak season
- > 2 fruits per type
Sample file attached.
I am trying to build conditioning formats for a Financial template.. This is my formula
'=IF(c21<0.1,"RED",IF(c21<-0.01,"BLACK",IF(c21<1,"YELLOW","GREEN")))
It should display the following. I would like to use the icon sets that are color coded. I seem to have some error or overlaps.
GREEN = On track
³1.0
YELLOW = Slightly behind schedule/budget
³0.85 but <1.0
RED = Needs immediate attention
³0.65 but <0.85
BLACK = Needs to be killed or restored
<0.65
I have a column in a spreadsheet that tells me when we ask for a check. I would like to take another column which has the date of when we receive the check to change colors based on whether it is within 30 days of the "ask for check" column and then another color if it is over 30 days. Let me try to do an example below:
ask for check Date Received
1-10-12 1-30-12 would be blue because it before 30 days
1-1-12 3-1-12 Would be red becaue its over
1.- I have in any cell the name of the month ( example :A1 )
2.- In other cell I have to put this words . . " YES" , ( example B02).
3.- I need the cell "A1" fill with a color
a) if in the cell "B2" dont have any word... then FILL the cell "A1" with Yellow color
b) bu if in the cell "B2" I put the word "YES" then do nothing with the cell "A1"
How would I make cells in column A to C be highlighted yellow, of which is 0.02 in value be higher than in compared with the adjacent cell in column D ? It is easy to use condition format function to setup the conditions in 1 row. The problem is there are thousands rows in the sheet(of which represents an individual file by each row), and need to have the conditioning format applied on each row independently. How can it be done w/o the need to have format painter applied line by line, of which is a very time consuming.
View 6 Replies View RelatedIm working on a large workbook with multiple worksheets. Each of these
worksheets has a large amount of data relating to training records. In
particular, the date that an employees certification is due to expire
(eg: 01/02/2007 or 31/09/2009 etc)...(im also in Australia so my date formats may be different).
I need to color code these records with the below logic, eg:
If Cell Date is <Today() = Red Text or Cell
If Cell Date is <18months from Today()+1 = Yellow Text or Cell
If Cell Date is >18months from Today() = No change - leave white
I have basic VBA knowledge, but will be able to work my way through
some of the code that is posted.
It's also key to note that there are multiple 'Blank' and 'Text' Cells
in these worksheets. I only want to apply this code to a cell if it is
populated with a date, is this possible ?? (eg: i only want to change
the color of the dates listed, not the other cells with text and single
numbers).
I have played around with the Conditional Formatting but i will need
more than 3 conditions in the future. I've also attached a small
screenshot of the data for your convenience.
I am trying to look & match key values from 2 areas of one table with two areas of another table; in turn, it'd return one value based on the lookup table...
Attached worksheet : Test booklet.xlsx
I have attached sample sheet.
I need to populate the Rep Names looking up 3 Criteria (Client Id,Curr Cov Id,Dom Buy Grp Id) from the Table 1, either one matches the Rep Names has to populate.
The data has to be pasted on WIP_Sheet in different columns.
I have a bill of materials with a description column. I want to search that column for various words (ie. wheel, screw, spacer, shelf, etc) and return a value into another new column depending on that value (wheel inputs wheel, screw inputs hardware, spacer inputs hardware, shelf inputs shelf).
How Excel shows you how to search will only return one value because I can't use an else statement:
I would like to restore the currency format including the $ and three decimal point commas.
=MIN('2Data'!E2:E500)&(" - ")&MAX('2Data'!E2:E500)
Im using access 2003 and the conditional formatting is only limited to 3. But i wanted 5. I was told it can be done using VBA. But I've got no programming experience whatsover.
Basically this is what i want, but i dont know how to translate this VB
IF Cell Value = >1 then make the value "Bold", "Bordered", and "Fill" with Orange colour.
IF Cell Value = "H" then make the text "Bold", Bordered, and "Fill" with Green colour.
IF Cell Value = "S" then make the text "Bold", Bordered, and "Fill" with Yellow colour
IF Cell Value = "A" then make the text "Bold", Bordered, and "Fill" with Blue colour
IF Cell Value = "U" then make the text "Bold", Bordered, and "Fill" with Red Colour
I want to copy the values of a cell with the formatting. ok, this is easy with paste special and keeping formatting.
What I wanna do is ... lets say the value is 400 , applying (special) formatting to cell the value becomes 500400. When I copy the value I want to copy all the number and not just the 400 but the whole number 500400 as one wthout the need to apply format to the destination.
Column 2 is a calculated value (a sum of several other columns). I cannot get the code to work. When I enter the same value in the field it works, but not when it is a calulated value.
I know there must be a simple error in here somewhere...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 Then
Select Case Target.Value
Case Is > 382: Target.Offset(0, 0).Interior.ColorIndex = 3: Target.Offset(0, 0).Font.ColorIndex = 2
Case Is > 315: Target.Offset(0, 0).Interior.ColorIndex = 30: Target.Offset(0, 0).Font.ColorIndex = 2
I am working on creating a spreadsheet that can be updated by those unfamiliar to excel. I have a master list on one sheet and three separate lists for business, individual, and general. Each list has three columns giving name, address and postal code. There are numerous repeats on these lists and I wanted to hide any duplicates while keeping my cells aligned. So far I have been able to highlight any duplicates, but was interested in some function that would save at least one duplicate while hiding any additional ones.
View 2 Replies View RelatedI want to build a database where the user can select a specific time period. I have a static file with the time periods and the related values. For instance I have in column A1:A15 the time periods from January 2011 to Dezember 2011, but I have some dates multiple times as there are more values attached to it. For instance I have in the first 4 rows January 2011...what I want is when someone puts in the value January 2011 in cell B1 and Dezember 2011 in cell B2 that the whole static file gets copied to another location (including the multiple dates) displaying the chosen time period. Similarly if someone puts in the value March 2011 to November 2011, I want only those values to be copied.
View 4 Replies View RelatedI maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.
I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).
The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.
As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.
The log code in column "H" Is based on this criteria:
First Letter of the Unique ID in column "E" - O, M, or L
Program Type in Column "F" - U or R
1. O-U = U
2. O-R = RU
3. M-U = U2
4. M-R = R2U
5. L-R = R
You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.
This is the area where my skill at using scripting dictionaries fails.
The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words
if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")
So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)
Here is my code so far.
VB:
Option Explicit
Sub test()
Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long
Set dic = CreateObject("Scripting.Dictionary")
[Code].....
I have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?
View 1 Replies View RelatedI want my IF function to populate two cells with two values if argument for IF function is found TRUE. Is it possible?
IF(logical_test, [value_if_true], [value_if_false])
value_if_true = return multiple values in mutiple cells (for example put number 8 in cell A2 and number 10 in cell A3 if function is TRUE)
How would I do that? I tried putting IF(logical_test, (A2="8",A3="10"), [value_if_false]) but it is not possbile...
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
two formulas for one data set. The data is attached in the spreadsheet: "Product IDs". The data is a set of Master Product IDs (parent) and the Linked to them Products (children). I need to create a relationship between unique parents (Master Product IDs) and their children (Linked Products)
I need to create two formulas:
1. From the Data Set table, need to vlookup the unique value in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). So, the result will be as shown in Table 2.
2. From the Data Set table, need to vlookup the unique (de-duplicated) parent/children relationship in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). There are total 3 parent/children relationships in Table 1. So, the result will be as shown in Table 3.
In sheet1, I have the borrowers and outstanding per employee. In the Area Avg sheet, I have the average borrowers and outstanding in each area as this is the standard for comparison (I got this through the subtotal function).
Now what I have to do is in sheet1, format(change the color) each cell under borrowers which is greater than the average of the relevant area. Since, (in my actual data) the name of the area may be duplicate in different regions, the reference value (average borrowers of the area) from "Area Avg" sheet has to be traced through multiple criteria which are: Division, Region and Area. The same also has to be done for outstanding but the solution can be provided for borrowers only.
Some of my numeric values are with single decimals .....
Eg:
1542.2
9856.5
659855.9
2589.7
When I format in Excel with Number with 2 decimals my results remain the same
But the result which is need is:
1542.20
9856.50
659855.90
2589.70
I am having a very difficult time trying to figure out a conditional formatting formula for something that seems very simple. There are 3 conditions I would like to apply to cell B2: If the value of I2 is less than or equal to 24, but greater than or equal to 21, shade B2 red. If the value of I2 is less than or equal to 20, but greater than or equal to 12, shade B2 orange. If the value of I2 is less than or equal to 11, but greater than or equal to 8, shade B2 yellow. I am sure I am missing something quite simple here.
View 5 Replies View RelatedCan Conditional Formatting be set on multiple values? I cannot get this to work with any formula, the cell values are not between two values. For example: for value = D or R, or X change color to green. (I would have another condition within a range F to Q)
View 4 Replies View RelatedFor use in conditional formatting, how do you check three values are identical?
=exact doesn't work, x=y=z doesn't work.
Using conditional formatting to make all the cells that contain numbers and are formatted to percentage format make display in red color? let's say I want to set this conditional formatting rule initially after just opening the excel book and afterwards when I type in values/text etc. into the cells I want the cells where I have input numbers and chose the percentage format to come out in red color? and all the rest stay in black color?
View 6 Replies View Related
