I'm trying to do a simple conditional format where the date is highlighted in red if the difference between that date and Todays date is greater than 90 days.
I've done the formula as =(TODAY()>$B5)>90, which appears to work in the sheet and shows either True or False but when inputting that formula into the conditional format it highlights every single cells.
Are conditional formatting formulas different?
I am trying to use a combination of Concatenate and IF formula to produce an email.
My input.
D4: First Name:
D5: Middle Name:
D6: Last Name:
D8: Organization:
Once all these are filled, I want the formula to produce a result like
FirstName.M.LastName@Organization.com
I have used the following formula.
[Code] .....
Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.
FirstName..LastName@Organization.com
How do I remove the additional (.) in cases where there is no middle name.
Attached File : Email Generator.xlsx‎
If I type in an age in A1, in A2 I want an age group to appear
the groups are
20-24
25-29
30-34
35-39
40-44
45-49
50-54
55-59
60-65
so far, if I type in an age then it normally enters the group ABOVE what I want
i.e. if I enter “24” then it put the group “25-29”. if I enter “42” it puts “45-49” and so on.
=LOOKUP($A1,{0,16,20,25,30,35,40,45,50,55,60},{"<16","20-24","25-29","30-34","35-39","40-44","45-49","50-54","55-59","60+"})
My goal is to sum up the total sales and total forecasted sales of each cost center. I compiled a formula that is supposed to do this just as I want it to. Simple enough. However, for some odd reason the formula returns either the totals of ALL cost centers or just a zero instead of the correct result. The weird part is that when I check the formula by pressing the "insert formula" button it suggests that the formula actually should produce the desired result.
View 5 Replies View RelatedUsing Excel 2007.
I setup a named formula: Spread1 =Sheet1!$E$2:$BE$2 where I entered % values to spread a an annualized figure over 53 weeks
On Sheet 2 I entered the amount to spread in $D$2 and in $E.. I entered my formula: =Spread1 * $D$2. Everything appeared great!
But then I deleted a few columns on sheet2 so first calculation is now in $G$2
Spread1: =Sheet1!$E$2:$BE$2
Calculations: Sheet2! $G2:$BG$2 (the values in I,J,k while summing to the total are in the incorrect weeks based on the spread on Sheet1, BF and BG have #VALUE! errors)
Seems odd, if I highlight "Spread1" in the formula bar and [F9] I see an array of 53 possible values and the 3 values I expect are in the place in the array (elements 5,6,7).
How I can defeat it with the possibility that a user may delete a column?
I have created a macro some time ago that is an integrated part of an XLA. The Xla has worked fine but now, for some reason, the macro fails to import the specified text, it doesn't fail but nothing gets imported. I have tried solving this myself, but alas I am not bright enough
The code is:
Sub GetWorksheet()
Dim filetoopen As String
Dim wb As Workbook
filetoopen = Application _
. GetOpenFilename("XL Files (*.xls), *.xls")
On Error Resume Next
If a player does not qualify, their name and score should not be included in the final results.
I currently have their Name missing, but the NUMBER is showing up.
I've explained more in the attachment.
INDEXARRAY.xlsx
I have a problem with VLOOKUP function and I am looking for a workaround.
SheetA I have a column of business names and another column of the category.
SheetB has a list of credit card transactions and a column of categories - The results obtained from incorrect results from SheetA...
Hello, I cant figure out how to do this! I have a cell B2 that I enter a number in and in cell C2 I want it to add the number from B2 and add 15 to it. I can get the formula "=B2+15" and it works but I wont always have a number in B2 and I want it to say "0" and "0" would be in C2. Any ideas?
View 3 Replies View RelatedI have a range A1:D10 containing formulae. I want the cells to change colour depending on the formula result. Conditional Formatting will do the job but only up to three colour so I need to use VBA because I have 6 colours. For example, if the result is 1, the colour changes to blue, if the result is 2, the colour changes to green, etc
I have VBA code which will change the cell colour if I input a value, but not if the cell is relying on formula result. The formulae results are dependent on cells throughout the workbook. The formulae results can be letters or numbers, whatever is easier.
s/s is very large and the need is to transfer formulas from one column to another. Column L contains formulas in cells L7:L45 (attachment) - (L7:L326415 in working s/s). Some of these formulas result in a value being given. I need to transfer formulas only from those cells having values to cells three columns to the left on the same row. I do have code which I was using for another application. This puts a formula in the correct places but it is the wrong formula and I don't know how to amend it so that it carries out the required action. Small attachment enclosed for better understanding of what is involved. Or could anyone change the line ".formula = ......" in the following code to make it work??
View 5 Replies View RelatedI have a workbook that if I use just the array formula for the totals it works fine and if I use data validation and vlookup and the array formula for the totals it is giving me a #Value! instead. I have tried a couple of things and did find that as long as I do not use the vlookup in the one column (column d) the array formula at the bottom works but as soon as I add the vlookup to column D I got the error.
I want to use the vlookup to get the values for items on a different page - along with the data validation and then use the array formula to get a total for multiple items in the above columns. I have attached a workbook.
I am using VBA to export an access query into excel, the query works fine in access but when importing the data into an excel spreadsheet, it doesn't display the date column headers in the correct format.
The problem I have is that the dates 1 to 12 are displayed the wrong way round eg:
In Access date column headers from query are:
09/01/2008, 11/01/2008, 12/01/2008, 14/01/2008, 15/01/2008 etc.
But when exporting to excel, the above dates are shown as:
01/09/2008, 01/11/2008, 01/12/2008, 14/01/2008, 15/01/2008 etc
It seems to be changing round the dd/mm when I export, but only upto 12 when the day is 13 or more it is displayed correctly.
Public Sub bttnDMA_Click()
'DMA Figures in Excel format
'Creating the Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim MyRecordset As New ADODB.Recordset
MyRecordset.ActiveConnection = cnn
Dim MySQL As String ....
I have a form on which I select a date (using a calendar form) and on the form it works perfectly. The date is in the correct dd/mm/yyyy format for example (03/04/2013 - 3rd April 2013), I then insert this date into a worksheet and it comes out in the correct format, but it is actually turning the dates around so where it should be putting 3rd of April (03/04/2013), it's inserting 4th March (04/03/2013) instead. It looks very much like the form sees the date in dd/mm/yyyy format and the worksheet when taking the data from the form thinks it in US format (mm/dd/yyyy).
[Code] .....
I did a data table yesterday and it worked. I tried again today and the results are incorrect. They are coming out as a constant (the same result as the original formula). Has anyone had this happen before and figured out how to fix? My spreadsheet is fairly complex. Does the data table formula need to refer to the "base" cells? For example, if the formula refers to cell F15, but in F15 the formula is +C15, does my data table formula need to use C15?
View 3 Replies View RelatedI have a file with two columns
Column A has a list of equipment names, for example: EQ1, EQ2, EQ3, etc.
Column B has serial numbers of equipment from column A.
I need to create a complex conditional formatting, which will check:
If A1=EQ1, and B1"HCC*" then make it red,
OR
if A1=EQ2, and B1"ABC*" then make it red,
OR
if A1=EQ3, and B1OR("CDE*","FGE") then make it red.
And then same thing for A2, B2 cells.
I have thousands of rows in both columns.
I have to check whether selected equipment name corresponds with entered serial name pattern. I don't know how to do it for each row separately.
I thought of something like:
1st condition: =AND(A1="EQ1",LEFT(B1,3)"HCC")
2nd condition: =AND(A1="EQ2",LEFT(B1,3)"ABC")
...
But how do I apply this conditional formatting to each row, so that it will compare A2 with B2, A3 with B3, and so on, instead of comparing A1 with B1 in all of the rows?
I was wondering if anyone knows if you can set conditional formatting if the cell contains no formula but is not blank.
We use many formulas to calculate cells and we need to the cell to be colored if there is no formula entered. But the cell is not necessarily blank or zero.
I have a spreadsheet where I have certain cells that contain a link to copy a number from another tab in the spreadsheet.
At times, I have to plug in a number to try certain values. In doing this, I overwrite the link or formula. I then do an 'undo' to bring it back to the original link or formula.
I would like to put a conditional format based on the cell's content. If it is the formula, leave it as is. If it is overwritten by a number, I want it to change colors to remind me to undo my change when I am done.
The conditional formatting options I have found so far allow me to format according the the value in the cell but not the nature of it's content: number of formula.
I have a set of data in A:P
I have a named range "TR" in which I need to look up a value
I would like to conditionally format Columns M:P if the value in the cell is less than a value in the lookup table
Column M corresponds with the 2nd column of the lookup table
Column N corresponds with the 3rd column of the lookup table ETC
My formula would be:
if M2 < Vlookup(b2&g2,TR,2,false) then conditional format
if N2 < Vlookup(b2&g2,TR,3,false) then conditional format
etc for Columns O and P
I have attached a sample workbook. TEST CONDITIONAL FORMAT.xlsx
I would like to combine a formula with conditional format:
In P10
If I4 = "FPI/FPI" and P10 = "Y", then the cell pattern is a different color.
How to conditionally format an array formula? The basic formula is an index(match criteria1 and match criteria2. I have come across sites where they mention you can incorporate aggregate into the formula to remove the Alt+Ctrl+Shift thus allowing for the formula to work in conditional formatting. Shown below is the formula I am trying to use. Basically if the formula below = today() then be yellow.
{=IFERROR(INDEX(Table2[Comp Req Date],MATCH(1,([@[SLDR_EF]]=
Table2[Component Material])*([@[Grand Sequence]]=Table2[Order Seq Num]),0)),"")}
I'm using this formula in a Conditional Format rule.
=CELL("row")=ROW()
Can anyone help me modify it, so that only the active cell is formatted instead of the entire row?
I want to gray out a row of cells (B11:M11) when "x" is entered in cell M11. Suggestions for using Conditional Formatting or a formula would be great. I have tried a couple of formulas but none work.
View 9 Replies View RelatedI want to create a conditional format formula which is based on values in 2 cells.
Example:
Cell B1 = 100
Cell C1 = 50
I want a formule for cell A1 which turns red for exmaple when B1 is higher then 85 and Cell C1 is lower then 85. Only in this case the conditional format should work.
I have a large spreadsheet with a number of columns that are set up for conditional formatting. For example a column of "BUY"'s and "SELL"'s where the "BUY"s are formatted with the default "light green fill, dark green text". I have other columns that are conditionally formatted using a formula. When I base the result on a formula I don't seem to have the option to use the default formats. Is it possible to use the defaults or maybe even how to customize my format to look like the default? It would look a lot better if I had a uniform red/green style throughout the sheet.
View 1 Replies View RelatedI am using a conditional formula to compare if a cell in column A is higher than a cell in column B. If a cell is blank the cell is formatting. I am trying to highlight only cells with numbers. Any formula to place in the conditional format formula that will evaluate the cells excluding a blank cell?
ie: grapes and pears should be highlighted because value in column B is less than column A but the conditional format is highlighting apples also where apples should have no highlighting because the value in column B is none or blank.
A B
oranges 2.49 3.25
grapes 1.99 1.89
apples 1.00
pears 1.11 1.03
I am lookin to apply CF to a cell based on the value of the left two numbers in a concatenated formula. If the value of the left two numbers are greater than zero the apply the CF
View 6 Replies View RelatedI have a small spreadsheet setup for my job where i have to input the time on arrival in L3 which is linked to a NOW() formula. this is it (=IF(L3="",NOW()
View 9 Replies View RelatedThis should be pretty straight forward but the solution has escaped me so far. I have some formulas that ratably spread monthly budgets across the life of a program. Ocasionally these budget formulas are hard keyed over with a value which overrides the ratable budget formula. I would like to conditionally format all cells that contain hard keyed values so they don't get inadvertently copied to new budget lines.
View 8 Replies View Relatedi have the following formula applied to a conditional format, on row 12:
cell value > less than =($K12:Y$111)*(1-0.05)
i want to use this on all rows, however i have 300+ rows.
Is there a way to apply this to all rows between column K and Y, i don't like the thought of creating 300+ conditional formats!