I have a question that I've been struggling with for a few days now. I work with excel quite a bit so I'm used to setting up complex formulas, however I have something that has stumped me.
I have one cell setup with the following format for a phone number: (000) 000-0000. Now what I wish to do is mistake proof the cell and receive an error if it doesn't contain 10 digits.
If I type: =if(A1= (000) 000-0000, A1, "Incorrect") Then it will return as False because it's searching for that exact syntax. What I want it to do is have the cell recognize whether or not it contains 10 digits, so that if I mess up and have 9, it will easily be spotted.
I have a long list of dates that are fomatted as 3-12-13 that I want to format under custom date as d-mmm-yy : 12-Mar-13. I tried to do this but it does not change. I tried to change it to a number and that didn't work either.
I am using the formula =IFERROR((D10*0.5)+(E10*0.5),"") but in some field it gives me a percentage that rounds and others is giveing me a 4 digit percentage
So I am setting up a spread sheet that tracks time users play on a video game. I can check this info online for each users and the site has the play time in d:hh:mm:ss format. So now I have entered the info for the players and I need to create a formula to show how much time was played that week. Example
Cell B2 is time on 2/27 and Cell C2 is time on 3/6 in Cell D2 I need to show time played in between those dates.
B2 = 0:21:27:00 C2 = 1:01:14:00 D2 = ?????
Note: I have the cells Format as Custom and Type is d:hh:mm:ss
I have a workbook containing 5 sheets. Sheet1 is my results page and the balance contains employee names and dates training completed(matrix)
On sheet1 I have 4 columns - 1name, 2type of training, 3date completed & 4refresher due. I have created a combobox and used the vlookup formula to populate the cells within the third column but I seem to have a problem with the date format. In the 4th column I have used this formula (=c2+365) this displays a new date one year on, but if an individual hasn't completed training and the cell within c2 is blank it defaults to this - 30/12/1902. If cell c2 is blank I would like d2 to be blank too.
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?
In cell A1 is time now. =NOW() Cell A2 is a date in the future. I want to calculate remaining time in Months/days/hours/minutes format by subtracting A1 from A2.
******** ******************** ************************************************************************>Microsoft Excel - time left.xls___Running: 11.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD125/09/2008 16:49 212/10/2008 07:30 3? 4Result in A3 to be in months, days, hours and minutes format Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
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 am trying to format cells in a range that is not a formula. I tried a UDF and CF but would not work properly. Maybe due to a Bug in Excel.
Therefore created the code below:
I put this code in my ThisWorkbook Module.
What I am trying to accomplish is this.
Check the range of cells, if any cell is not a formula color the background Yellow and the foreground Bold RED.
The workbook has about 40 sheets 32 of them are formatted the same they are numbered "01-31" and 1 is "Temp".
This code works fine but the second procedure would not work when this 1 is there.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim myCell As Range Dim myIntersect As Range Dim myAddresses As String Dim SheetNamessToSkip As Variant Dim res As Variant SheetNamessToSkip = Array("Splash", "Menu", "Diesel", "Gasolina", "Alcool", "Prev Month", "Totals")
myAddresses = "G12:H51,P40:Q44,P48:Q60,Q14:Q17"
Both subs do what I expect them to do, but can not run them together.
I get a compile error when trying to do it this way.
I am trying to limit the format of data to all cells in a column to 2 letters followed by 6 digits. The letters and the numbers are not required to be the same, but the person entering can only enter in that format.
I am using the VLOOKUP function in a cell, however, I would like the value returned to also retain the text format from the table (eg colour and bold). I have attached a simple example (to demonstrate the point) where cell B14 should have the word 'French' in bold red.Example.xlsx
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.
In A1 have two words, say "first" and "second" as two lines entered by pressing Alt+Enter after typing the first word. The requirement is remove this formating OR to get these two words separately in single cells. ie B1=First & C1=second.
My formula works fine, but I need the first number of the answer displayed with only two decimal places. The formula averages a set of numbers, then adds " of " and a cell reference.
I want the answer displayed as 2.43 of 3. Here is the formula.
=IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123))&" of "& Z9,"") it works perfectly but displays 2.42857142857143 of 3.
The cell is formatted to NUMBER with two decimal places. . Changing the format has no effect, but changing the formula does.
If I use =IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123)) or =AVERAGE(W12:W123) the result is 2.43. When I add the next portion, &" of "& Z9,"") it changes to 2.42857142857143.
I've tried reformatting cells and nothing changes. This is the only cell I'm having problems with. The worksheet seems to be working fine.
how to change a proportion of text in a resulting concatenation formula.
cell A1 contains the text in bold "ABCDEF", cell A2 containes "GHIJKLM" cell A3 contains =A1 & " " & A2 but I want the cell A1 part to remain in bold. Is there a way of using the TEXT function e.g. =TEXT(A1,BOLD) & " " & A2?
Looking for a little help with the following formula =CONCATENATE("August 07 -",TODAY()," Evening Instructor") After entering this formula today is displayed as a serial number. I'd like it to display as current month/current day/current year (11/28/08)
is possible to create a formula in a cell (A1) that calculates the average from an entire row of cells (B1:Z1) BUT only considers those cells that are formatted as a percentage. The row (1) contains cells that have percentages, dollar values, zeros, and blanks. If, for example, the entire row of say 100 cells has just 4 cells with percentages in them, how to get it to identify those 4 cells and calculate the average?
I wish to compare 2 sets of data in 2 cells and in the third cell i wish to both display the difference and format in a colour depending on the outcome of the difference. Is this possible? or is there another solution?
e.g cell A1 is 21 cell B1 is 26 cell C1 is the difference and if the difference is greater than say 2 then it would colour red if equal to 1 then yellow and if less than 1 as in 0 -1 etc it would colour green. I have been very successfully using conditional formatting to colour a cell that has the data inputted but cant now do it for the formula cell. The context is pupil progress but that i guess is irrelevant really.
I need to underline text which is populated from a formula. How to format numbers, dollars and dates within a formula, but how to underline text.
Desired Result: The employee's who sold more than 100 cars this month are: Alex, Mike and Tim. Please congratulate them on their performance!
Formula being used ="The employee's who sold more than 100 cars this month are: "&cell reference&"Please congratulate them on their performance!"
Current result: The employee's who sold more than 100 cars this month are: Alex Mike Tim Please congratulate them on their performance!
Attempts: I wish underlining text were an option within Format Cells/Custom so I could just use the formula =text(cell reference, "underline"). However, I don't see that as an option in the 2007 or 2010 versions. Is there a way to add "underline text" as my own custom format?
I also tried using "Conditional Formatting" (both "Format cells only that contain" and "Use a formula to determine which cells to format)." I can't quite get it because I don't know how to list names for the former conditional formatting. Vlookup is the only way I know how to refer to a list for the formula based conditional formatting, but this would stop after the first name on the list is found and ignore the rest of them.
I have a workbook with several sheets, this question focuses on two sheets. 'Coded Prehospital Data' and 'Missing Pre Hospital Data', for brevity here i will refer to them as coded and missing respectively.
In coded the cells are populated using an index/match fx to another sheet and if that returns an error the fx looks to the same cell in missing for a value
Here is an example of the formula in coded cell C2:
=IF(OR(ISERROR(INDEX(MATCHAGEUNIT,MATCH(A2,MATCHCC,FALSE))), ISBLANK(INDEX(MATCHAGEUNIT,MATCH(A2,MATCHCC,FALSE)))),VLOOKUP( 'Missing Pre Hospital Data'!D2,AGEUNITSCODE,2,FALSE),INDEX(MATCHAGEUNIT,MATCH(A2,MATCHCC,FALSE)))
Without having to constantly switch between sheets I would like to set up a conditional format that fills a cell in missing yellow when that cell is an error in coded.
This is what I have done to achieve this with no success:
1) Select cell C2 in missing and add rule based on formula 2) Enter =OR(ISNA('CODED PREHOSPITAL DATA'!$C2),ISBLANK('CODED PREHOSPITAL DATA'!$C2)) 3) Enter the custom formatting I decided on 4) In the "Applies To" box I have done two things: 1) drag the cursor from c2 to an28 which auto fills the applied to dialogue box with 'Missing Prehospital Data'!$C$2:$AN$28 and 2) Free type in the dialogue box 'Missing Prehospital Data'!$C2:$AN28
My problem is that I need the formatting for each cell in missing to refer to its sister cell in coded but it continues to refer only to coded c2
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.
I have a formula that reads the date in Colum B on 3 different sheets. The data on the seperate sheets is pulled from a SQL data base and formats the date wtih the time after it.
11-13-2006 10:47
My formula is looking at dates and subtracting them to give a total count of the occurance of the word "Yes" between dates.
I am having issues with this sheet. i am trying to get this formula to stick, when i drag down or across. its not holding the formula true to the first column. i am unsure of what this is called or else i would be using more specific words.
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.
