I have a cells that are formatted for accounting with a formula in them.Whether the formula is evaluated or not, the £ sign still shows. Is there a way of only getting the £ sign to show only if the formula IS evaluated (i.e leaving a blank cell if the formula IS NOT evaluated)....
View 6 RepliesI have a sheet connected to external SQLSERVER data.
One of the columns includes a formula, but the formula is not evaluated but shown as text.
My challenge is making it evaluate (without using excel tricks like replace equal sign with equal sign or clicking each cell and approve it)
I tried going down the road of concatenating char(10) or char(13) to the formula string but no success.
I have a nested if statement that doesn't give a blank cell when it is evaluated. The cell is general formatted but results in a '0' in the cell instead of a completely blank cell.
If statement is:
=IF('Sales info'!B16="",'Sales info'!B6,IF('Sales info'!B6="","",'Sales info'!B16))
Why is this giving me a 0?
I have a table that automatically gets populated each month via VLOOKUP.
There are about 14 columns and anywhere from 80 to 130 rows.
At the bottom of each column I SUM the total for the column.
Because I never know how many rows will get populated I have my VLOOKUP formula go down about 180 rows, then I SUM at the 183 row. But because the formula returns N/A on all of the rows that do not have data my SUM in row 184 returns N/A. This means the end-user has to go back in and delete all of the rows that have N/A. I don't want them to have to do this. Is there anyway to format the cells so that this does not happen?
I am trying to conditionally format some data in cells A3:A6 (shade the cells) depending on whether a number in B1 is 1 (green), 2 or 3 (blue), or 4 (red).
View 3 Replies View RelatedI am having some issues with my conditional formatting code. I need 5 condtions, so I cannot use the built in Cond. Format. Column E is the average of cells F,G,H and when I update the numbers in F/G/H the background color is changed to the appropriate color, but column E does not change color!!! Example: The average (E) will start as a 90 and have a red background, then when the inputs(F/G/H) are changed and the average(E) now equals 30 but it is still shaded red! Is there anyway to have every cell updated, to make the cell change from Red to Green automatically??? Below is a copy of my code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As Integer
If Target.Cells.Count > 1 Then Exit Sub
CellVal = Target
Set WatchRange = Range("E6:H15")
If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case Empty
Target.Interior.ColorIndex = xlColorIndexNone
Case 0 To 19
Target.Interior.ColorIndex = 8
Case 20 To 39 ..........................
I need a formula for conditional formatting which highlights a row if A has the text "Ex" and B has "Paid".
View 2 Replies View RelatedI had a list of people on sheet1 of my workbook, and on sheet 2 a table with a list of names and their department.
I started off by wanting to use VLOOKUP within the conditional formatting feature of Excel to find anyone who worked in "finance" in sheet 2. I could get this formula to work on the sheet but as soon as I put it into the conditional format feature it wouldn't work.
Trying to find a work around I added a column in sheet 1 where I inputted the VLOOKUP formula and caused it to display "1" if the person worked in finance. It looked like this:
A: Person's name
B: Value of 1 or 0
The idea was to hide column B and use conditional formatting to say "if column B2 = 1 colour column A2 blue". Now I could get this to work on a single row but couldn't make it work over a range, ie. "if cells A2:A150 have a 1 in the column next to them colour the relevent cell blue"
I feel I'm missing the obvious. I found quite a few answers online but non actually specified how to get the conditional formatting to recognise the relevent cell it should be looking at in a range.
1) How would I get conditional formatting working over a range
2) Whether it is possible to use VLOOKUP within the conditional formatting function of Excel.
I need conditional formatting formula that evaluates the values in a row of cells (7 total columns) and counts the number of entries that are 30 or greater. If there are at least three, then I need to apply formatting.
I just can't get the formula right:
Code:
=(COUNTIF(B7:H7,
I have attached a small file with a very simple conditional formatting formula used to turn a block of cells green.
However, the formula does not seem to work properly as it only turns half of the cells green. Columns B1 to B18 are also supposed to turn green, but instead remain clear.
I need to be able to get a macro to do this. I recorded the macro. The conditional worked, but when I cleared it and then ran the macro, it highlighted all of the wrong stuff. Here is what I want to do: In column(AJ) I have cities with Prefixes or not In another column (AV) I have Criteria1 and in yet another, I have criteria2 (CB)
City
(AJ) Criteria1 Criteria2
SCRNRTH_SCRM1 FALSETRUE should fail test
A9_SCRNRTH_SCRM1FALSETRUE
A9_SCRNRTH_SCRM1FALSETRUE
A9_SCRNRTH_SCRM1FALSEFALSE should fail test
SCRNRTH_SCRM1 TRUETRUE should fail test
A9_SCRNRTH_SCRM1FALSETRUE
SCRNRTH_SCRM1 FALSEFALSE
A9_SCRNRTH_SCRM1FALSEFALSE should fail test
A9_SCRNRTH_SCRM1FALSEFALSE should fail test
A9_SCRNRTH_SCRM1TRUETRUE should fail test
A9_SCRNRTH_SCRM1FALSETRUE....................
I have a spreadsheet filled with formulas that depend on a value being entered into A2, A3, A4, etc... So column A starting at A2 is where I will manually input a number and the formulas I have in columns B, C, and D will import information from another sheet based off what is put in column A. In column D the formula I have to import data
is =IFERROR(VLOOKUP(A2,Master!C:M,11,0),"").
This will import another number. Additionally in column D, I have conditional formatting that will return a red, yellow or green light based off the rule I have in place. Everything works fine, the only problem is that column D has a green light all the way down even without a value being placed in column A. I would like to find out a way to keep the cells in column D blank until a value is entered in column A. Also, if I go back and delete the value in column A, I would like the corresponding cell in column D to go back to blank as well.
how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:
For cells M8:EK8, my conditional formatting
condition 2: Formula Is =AND($E$8>=M2,(($E$8-$D$8)>=(N2-$M$2))), color index is 40
condition 3: Formula Is =AND($F$8<=M2,$G$8>=M2), color index is 39
I want to add:
condition 4: Formula Is =AND($H$8<=M2,$I$8>=M2), color index is 40
condition 5: Formula Is =AND($J$8<=M2,$K$8>=M2), color index is 39
and so on
The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.
resource tracking ex.jpg
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
Working in Excel 2007. I am using excel for a data log (basically) and want it to format all empty cells in a row yellow if there is data in column A
Basically, If i have a value in A2, I want any empty cell between B2-G2 to be filled in yellow (as an idicator to the inputter that the cell needs to be completed).
there is already conditional formatting on these cells, which i want to maintain for the non-empty cells. I also have "0" as a value, so I couldn't use the basic conditional formatting setting it =0, it highlighted cells with $0.00, which i do not want.
Is there a way to set up a conditional format for several cells so that the cells are filled in with red until the user enters text in those cells??
View 5 Replies View RelatedSo i've been trying to do this invoice/inventory/client , I couldn't find a way to somehow register the whole invoice as it is, so i can later print/visualize it if i lost the copy or something.
It would be best if it just stayed stored in a variable and not always visible, and only appeared if i wanted to check/print it.
The whole invoice would be from A1 to F30
Excel 2010 btw
What I am looking to do is;
If cell A2 = "Closed"
then I want cells B2 and C2 to strike through its own text.
I.E.
A2= anything other than "Closed"
then B2 and C2 = Blah blah blah
but if
A2 = Closed
then B2 and C2 = Blah blah blah
i have just read the thread started by scabertrain regarding "if" conditonal formating, but mines slightly different and i cant make head nor tale of the fomula shown.
So... Monthly budgeting... using a projected outgoing (A1) and an actual outgoing (A2) i want the colour "A2" to change depending on whether i have spent over the budgeted amount, green being underspent, orange for on target and red for over spending....
Example:
I predicted (A1) that i was going to spend £50 on a night out, but my actual spending (A2) was £100, i would like A2 to turn to red.
I have attached the sample. I need the cells without the employee or without a boss to highlight a color and i also need the date of certification to highlight if it is more than one year old.
conditional formatting.xlsx
Creating a project tracking sheet that is as automted as possible so that people dont have to populate lots. I have managed to get it to create a new sheet and populate according to a filled out combobox. The user then populates a 'milestone' section. When a button is clicked these are transferred to a GANTT chart type sheet and pasted. Before they are pasted the macro checks if the project title exists, if it does not it first pastes the title LEFT INDENTED. Then follows by pasting the milestones CENTRED. If the title exists, it inserts the milestones under the project title by copying and pasting all milestones. Hence, if milestones are regularly added, it will begin to add duplicates of the same milestone. I need to remove these. I would usually be able to do this however there is often the same milestone in numerous projects which I CANNOT remove as it would loose data. I would like a code that says between the cell I was searching for (Rng) and the next cell which is also left indented to remove duplicates. Basically I can't use x1enddown because it needs to stop at the next cell which has the same formatting... i.e. only duplicates to be removed out of the cells which are centred. I would then like it to repear this action for the whole document and move the next left indented cell and do the same.
It feels like a kind of backward conditional formatting is what I am looking for
Here is my code as it stands
Dim FindString As String
Dim Rng As Range
FindString = Range("D2").Value
If Trim(FindString) <> "" Then
[Code]....
Have 2 conditional formattings going on in a database query and need to apply two more conditions but only have 1 conditional formatting left. I am trying to highlight everything in a row that contains either "HCAT" or "HCAL" in column D. So far I have this,
=$D2=OR(HCAT,HCAL)
but its not highlighting anything, any clues?
I am trying to conditionally format using AND() to reference if there is a value in the top line of a table and the title in the right most column for each cell in the table, the formula I am using works when i paste it into cells (i get the correct TRUE or FALSE for each cell in the sheet) but is not working as a format formula, I dont get an error but the shading does not happen.
The formula:
=AND(OFFSET($A$1,0,(COLUMN()-1),1,1)"",INDIRECT("a"&ROW())="Item1")
I simply need to have column A be red if value is < Column B value and green if it is >. The problem is that if the value in column A is .00000004 and column B is .00000005, it turns red. Example is the first three numbers below the headers....they each are 0%, yet the value on the left turns red because it is .0000005 more than the value on the right. I just want the colors to reflect the numbers which show up as below. Is there a way to do this?
2011 % of PDD
2011 % of Total $
0%
0%
0%
0%
0%
0%
[code].....
If I want to include the value of cell C4 in a formula, is it possible to keep the formatting ?
Cell C4 = 10,000.50
If I use a formula :="Profit for the Year ="&C4
ResultProfit for the Year =10000.5
I have a macro that pulls the date from cell C9. The formula I have in C9 is =today(). In the cell it shows todays date the way I have the cell formatted. MMM-YY. Which is The formula bar is displaying the formula in some and the date in the form of mm/dd/yyyy.
I need the formula mmm-yy to be in the cell and have the cell and the formula bar display Nov-13 in this case. The reason being that the macro is reading from the formula bar, usless I can change that some how?
1. I have a range of random numbers within a column, i.e. A1:A9
2. A specific number will appear in a cell at the end of that column, i.e. A11
3. I need to see via conditional formatting which of the above cells add up to that number.
I am aware that given the numbers in A1:A9, it is possible to have more than one combination of numbers that add up to A11. I would need to see those combinations as well identified using Conditional formatting.
I am trying to figure out if there is a way to round numbers to the nearest thousand without using a formula. I really want to be able to round just as you can set the number of decimal places. I need to be able to do calculations with the actual numbers, but I want to be able to look at rounded numbers.
I am referencing a cell in order to change the background color of a row of cells if the number in column B is = to the number in a specific cell. The formula used in the conditional formatting is =$B73=$B$10. The reference cell $B$10 contains the following formula =INT(MAX('Assumptions & Inputs'! F8:F9))
The conditional formatting worked fine when I used that formula. I had to change the formula in cell $B$10 so that it would meet a new criteria. When I did that the conditional formatting would not change the color of the row. The new formula that I put in the reference cell $B$10 is
='Assumptions & Inputs'!$D$8+MAX('Assumptions & Inputs'!$F$8-'Assumptions & Inputs'!$D$8,'Assumptions & Inputs'!$F$9-'Assumptions & Inputs'!$D$9)
I have a challenge with conditional formatting. I have products A-K with sales current year and last year and want to have year on year growth. I want to have one column where the cell formatting changes based on data validation.
Let's say product A sold 60 items CY and 50 LY meaning YoY variance is +10 items or +20%. Hence, if the relevant cell says "%" then the YoY figure should say "+20%" (formatted as a percentage) and if "VALUES" then YoY should be 10 (formatted as numbers/absolute values).
Remember also that YoY growth can be +100% (or more), 0% and -100%.