to set a conditional format and include a function code in it as well?
I want a spreadsheet to change the color/font of a cell/column based on a comparison of a "start date" cell and a "due date" cell. This is to automate the process of highlighting items that have gone past the due date in the second cell. I was trying to do this by looking at the "now()" function and comparing it to the due date.
I have a very large Excel spreadsheet that is generated through an Oracle application at least once a month at work. I would like some code (either a worksheet function or a VBA) to check and report the following. If the frequency in column C is "Monthly" or "Weekly" just go to the next row. If the frequency in column C is "Annual", add 183 days to the date in column B; if it is "Semi-Annual", add 92 days to date in column B; if it is "Quarterly" , add 46 days to the date in column B; if the frequency in column C is "2-Year", add 365 days to the date in column B. Once the check has been made; I need the new total or date that was calculated checked against the date the report is being run (system clock date). If the date or total days is less than today, a new worksheet, titled "Late" needs to contain that row of data. If the date or total days is greater than today's date, just go on to the next row. I've attached an example worksheet.
View 9 Replies View RelatedI am trying to set up a spreadsheet to track certification expiration dates and things of that nature at my volunteer fire department. At my career job, we use specialty software like Firehouse, but that is way too expensive so I must settle for Excel..or maybe Access.
Basically, I need a formula that will look at todays date and compare it to the date in a given cell and then somehow differentiate thise which are expiring at a given intercal.
For example, if someones EMT was expiring in 6 months it would turn yellow, then at 3 months turn blue, then red at 1 month then black if it expires.
It don't necessarily have to be just like that, just some way to differentiate depending on the how close to expiration it is.
I want to take a date (ex. May 25, 08 is in cell A2) and calculate 30 days (A2+30)June 24, 08(A6) and what i am trying to due is calculate if it is past due.
I used the if command but it does not seem to be calcualting the date plus thirty, it is only taking the date that is listed. (ex. instead of calculating =IF("A2+30"
I am trying to create a spread sheet in excel that will show a number of days past due counting from the due dates. see the attached xls file for details.
View 5 Replies View RelatedI have a simple table for tracking performance evaluations with 3 columns:
Employee Name (A), Date of Hire (B), Eval on File (C)
Evaluations are completed 6 months from Date of Hire (1 time) and on an annual basis and logged into column C.
I need to identify a list of evaluations that 30 days past due from the beginning of each month on a monthly basis.
I spent hours breaking the process down into steps (by adding extra columns to calculate 6 month due date, difference between current date and 6 month, due date for current year, and it still takes a long long long time.
There must be an elegant formula there that can tie it all together. I worked hours to research it and failed.
I put a scenario below to illustrate my table. As of 02/01/14, Anna Li's 6 month evaluation is past due, James and Johns' annual evals are past due as well.
Employee NameDOH Perf Eval
Anna Li 6/8/2013No
James Hawkins12/12/200612/12/2012
John Silver 1/8/20071/8/2013
Kim Woo 8/14/20078/14/2013
Sam Shell 6/6/20056/6/2013
Tanya Smith 7/1/2013 1/1/2014
I need a formula that can count items that are past due by up to 30 days, 60, or 90 days. I'd guess the total of all 3 values should = the total past due.
I have the total past due as =COUNTIF(A!I$1:I$278, "<=" & TODAY() )
Through VBA I am trying to filter for today's date and all dates 30 days in the future as well as all dates 30 days in the past.
I am currently using the code below, but it only show dates 30 days in the future. I cannot get it to populate cells that are either 30 days in the future or 30 days in the past.
Rows("4:4").Select
Selection.AutoFilter
ActiveSheet.Range("$A$4:$HQ$1000").AutoFilter Field:=4, Criteria1:="TBD"
ActiveSheet.Range("$A$4:$FQ$1000").AutoFilter Field:=12, Criteria1:= _
">=" & Date, Operator:=xlAnd, Criteria2:="
I am creating a tracker to record, incoming, latest date of reply needed and actual date of reply. So I have column A= received, Column B = need to reply by, Column C = replied on. What I want in A is today’s date which is easy enough with ctrl+; is there a way of having it set that as soon as the cell is clicked on that date is activated and until then it remains empty?
What I have in B is A# + 20 this adds 20 days to the date entered in column A, again is there any way to have it set that this automatically activates when cells in column A are active and not until then, as if I enter that code in the cell and nothing is showing in cell A it gives a date of the 20/01/2008.
In C I have tried a couple of codes and they have not worked so what I would like is a code that when A and B are active it generates a countdown for days left to reply and if this countdown reaches zero then the whole line is flagged up in red as late. Lastly, is there a code that I can use that will automatically correspond with the A, B or C columns and match them with the same number cell in the other columns, sorry this isn’t so easy to explain but what I have to do at the moment is type in B is =A1+20 , A2+20, A3+20 and so on rather than just typing in A……+20 for column B to know it needs to associate itself with the same number cell in column A regardless what number it is.
I need creating a formula that combines the following if statements in cell C107:
=IF(AND(B107
I'm brand new to using formulas for conditional formatting. How to achieve the following...
Red highlight for todays date and past dates
Yellow highlight dates up to 30 days before today
No highlights for blank cells or text
I'm using excel 2010.
I have spent too many hours Googling and trial with mostly error in attempting this. I formatted the very first cell A1 for today's date =TODAY(); this is also dirrectly above the column with all the dates in it. I just want an entire row's contents to turn red if it is 15 days old or older. This =$A1<TODAY()-15 somewhat works, but it only turns the date cell red, not the whole row. It also makes blank cells turns red, which I would like to remain blank (another formula is needed I imagine).
View 3 Replies View RelatedIdeally I'm looking for a way to do this with formula's but for the life of me I just can't think of one. Attached is a sample that explains what I'm trying to achieve.
View 7 Replies View RelatedI need to search through a worksheet for a value located in the first A column eg. and then put down a flag ( or bettar the searched text ) to che previous row in B column
Es:
I search the text 'xxx' in A column
if I found the searched text 'xxx' in A5 i need to have a flag or better 'xxx' in B4
Sorry for posting a related question earlier, where I forgot to specify something. I need to find the max in a column of data without doing any filtering or rearrangment of the column -- can this be done, either indicating the max across many columns by highlighting the max box with color or by writing its address in a new box in the bottom?
View 2 Replies View RelatedI am using a CountIf formula to flag duplicate entries in a column of data, in a Column I have "=COUNTIF(P:P,P1)" It returns a number equal to the number of repeats of the data in call "P1". I then sort by that column to isolate all rows which have duplicates in Column "P". The cell has "1" if there are no repeats, "2" if there is one repeat, "3" if there are two repeats, etc. What I would like is a formula which would put a "1" in the cell the first time it sees data, then something else when it sees repeats. That way when I sort, ALL the repeats will be together and can be deleted. Is that possible?
View 7 Replies View RelatedI am working on a big data spreadsheet 166 columns X 2500 rows. I need to flag rows that have 12 consecutive cells with a zero.
View 9 Replies View RelatedOur school system is trying to clean up student records. They have a demographic worksheet of hundreds of records. Each student has a student ID number. We are trying to flag students whose ID numbers in 9th grade do not match an ID number for 10th grade. so we are left with only students who have matching ID numbers for both 9th and 10th grade. We are trying to find the right function(s) to make this work without VBA. I am attaching a small sample file
View 7 Replies View Relatedsorting duplicate e-mails across three columns in an Excel spreadsheet.
Precisely, I have three mailing lists (Column A, B, and C) that I would like to sort.
I would like to know what e-mail addresses appear in more than one Column (Mailing List), and I would like to highlight/flag them somehow.
I have attached an example spreadhseet that contains fake e-mail addresses for test purposes. As you can see, some e-mail addresses are duplicated or in triplicate across the 3 Columns. In other cases, an e-mail address may be unique to a specific Column.
In my real spreadsheet, I have approximately 3,500 rows and 3 columns.
I have many small spreadsheets of data organised into 4 columns and anything upto 250 rows. I want to be able to test if all the cells along each row have the same data in each of the 4 cells, so 4 occurences of the same thing. Each row of data will be different. (I'm not intertested in matching the data going down the column).
However, I won't know with each spreadsheet what is the exact data I will be looking for - if all 4 cells don't have the same data, that is fine as this will then require my manual attention which is the purpose of my project.
I just want to avoid having to check through lots of rows of data where all the data does already match.
The data will usually be text but it can be IP addresses and numbers too. I need a formula or set of operators to use for is exercise.
Is it possible to run a macro from an excel cell formula?
ie.
=IF(A1=1, RUN MACRO, 0)
I am trying to write some VBA code for Excel that looks at a several different cells for a specific value and if that value is there returns a prompt message outlining to the user specific cell info. For instance, if some Excel cells in a column show that someone has not paid their bill yet, I would like a message to appear as soon as one opens the spreadsheet showing the bills for what customers are still unpaid.
View 4 Replies View RelatedIs there a way to flag data that hasn't been used in another worksheet. For example, a second worksheet consolidates the data into two different groups, but someone spelled the name of one of the groups wrong in the first worksheet and the data was not added via a sumif function.
View 2 Replies View Related1. In neighborhoods that have zero units in a given price range I have it to display "-" , because this unit is not actually zero, the data is not available. Therefore a #VALUE! is displayed for the percent because it cannot calculate the "-". How do I get excel to glance over "-" and flag it for no calculation?
2. For the percentages I am having to manually do them row by row. I would like to set it up in a manner that allows me to copy the formula down by column and across by row correctly.
For instance in the percent for Mira Lagos I have =B4/N3 where b4 is the units for mira lagos and n3 is the total. I can drag that formula across by rowto get all the correct percentages for mira lagos price ranges only, but I cannot copy this formula down by column to any of the other neighborhoods. In otherwords I have to do a new formula for each subdivision.
e.g.
Grand Peninsula=B5/N3
Meadow Glen(Mansfield)=B6/N3
...etc
Again I would like to make it so I can copy the formula across by row and down by column so excel will automatically compute it.
Assist me in developing a macro that would search for unique values in column A and then place a "1" in column N of that row to signify it as the unique value. I am trying to do this in order to get a unique count of projects listed because the list I receive has multiplie occurences of the same project # in column A.
I have an Excel spreadsheet with 3 columns (A, B, and C) and 600 rows. Each cell in A and B contains a 6-diget number. Column C is empty.
I want to determine whether the number in A1, A2, ….A600 appears anywhere in column B. If A(n) does appear in column B then I want to enter a symbol (say a Y) in C(n). If A(n) does not appear in column B then I want to enter a different symbol (say N) in C(n).
What is the formula I enter (and where) that will do that?
I have 2 named ranges that are one besides the other - let's name them tTableA and tTableB. I also have a strValue, which holds a String I will be searching for in tTableA.
Now, tTableA contains names (strings), while tTableB contains quantities (numbers) for the corresponding names.
I need a Button that, when clicked, will do this:
1) Check to see if the strValue string is present in tTableA:
1.1 If NOT present, tell the user "Error".
1.2 If present continue
2) Now that we know strValue exists in tTableA, find the corresponding quantity located in tTableB (this quantity would be on the same 'row' as the row in tTableA which contains strValue)
2.1 If quantity <= 0 then tell the user "Nothing left"
2.2 else REDUCE that quantity by 1 unit.
I am currently trying to work out what the best way would be to search between two sheets and find out where the differences are. IO have sheet 1 and 2 which both have account numbers and details on. I searching off the first column on both sheets and trying to identify which records exist on sheet 1 but not on sheet 2. The code I have so far is
Sub check()
data_sheet = "Sheet1"
target_sheet = "sheet2"
rowcn = 2
Do
If Sheets(data_sheet). Cells(rowcn, 1) <> Sheets(target_sheet).Cells(rowcn, 1) Then
Rows(rowcn).Select
Selection.Font.ColorIndex = 3
End If
rowcn = rowcn + 1
Loop While Sheets(data_sheet).Cells(rowcn, 1) > 0
End Sub
This currently works 50%. Only problem is that this code is not independant to each list, it simply looks at the same cell reference on both sheets and check whether the value is there. It doesnt actually go down the whole list and checks whether it is there.
I have been trying to work on this thing for like 3hours and i could not make even a single line since I dont get what loops I should make and how I should do it. Could someone suggest me how to do this.
1. check the date.Calculate two weeks backwards.
2. Check for id no which is scanned more than once in that two weeks from the current date.
3. Check for type of ppe if its different then leave it if they are same and exceeds 3 of the same type it should BOLD the badge id.
Have attached the sample file.
I'm dealing with a lot of spreadsheets put together by people who are not that familiar with Excel. Because of this, there are many instances where the formulas created include constants. For example: = A1 * B1 + 200. I want to create a VBA routine that will cycle through the cells I selected and "flag" those cells that have a constant in the formula.
View 3 Replies View Related