Traffic Light Icon Based On Dates Historic - Present And Future
Aug 15, 2012
What I need to do it create a traffic light icon, just the ones in Excel based on the dates within the field. If there is a date present which is past todays date then I would like it to display the green traffic light icon, if the date present is scheduled for a future date I would like the amber traffic light to show, and finally if there is no date present at all I would like it to display a red traffic light icon.
I'm trying to create a dashboard with traffic lights displaying the status of a task. My manager wants a to be able to pick a color (Red, Yellow, Green) from a drop down menu and then have that be displayed as a red, yellow or green traffic light. Using the conditional formatting option, I'm only able to use numerical values or a formula. I'm hoping there's a way to either create a drop down list with the 3 different colored traffic lights or a way to use VBA to have the conditional formatting search by text instead of numbers.
I am seeking to insert an image into an Excel cell by a formula or a macro.
I am currently trying to have a green traffic light appear in a cell if another particular cell has the numerical value of "1". for example, something along the lines of: =if(A1=1,"image1.jpg","").
Now, i know the above formula doesn't work but is something similar possible?
I am wanting to have conditional formats so the cell fill in a range on sheet 1 will change to:
- red if the value of a cell on a seperate sheet (date) is greater than 18 months old - yellow if the value of a cell on a seperate sheet is 'In progress' - green if the value of a cell on a seperate sheet (date) is less than 18 months old
I'm trying to work out to most efficient way of making a quality monitoring system which uses traffic lights to show problem products.
The traffic light system need to obey the following "rules".
Each product is tested once daily When a product fails once it moves to Amber, When a product Fails twice in a row it moves to RedOne pass when a product is in Amber Status, will reset it to green.If a Product is in Red status, it much have 3 consecutive passes to reset to Green.
I have tried to use array formulae to look up a column for previous values in the past for this sort of thing, but with 100000+ rows it runs far too slowly.
Banana Problem? Product Result Traffic Light (0=green, 1=amber, 2=red)
I have a scenario where I am trying to work out on a spreadsheet how many failures I have had according to a traffic light system. If I have 5 or less failures it is considered green, between 6-10 amber and 11 or more red. How can I create a formula under column A that captures which status it is on?
I am trying to apply icon conditional formatting in a cell. The cell contains the following formula: =VLOOKUP(D20,'owssvr(1)'!O:W,9,FALSE The formula results in a "2", "1", "0" or "-1" in the cell. The icon conditional formatting is not working at all (no icon appears). I have the conditional formatting setup as numbers Green 2, Yellow 1,0 etc based on value. If I delete the formula and just type in any of those numbers directly, it works. I have changed my cells to "number" and it still does not work.
I have a table that includes 2007 data and 2008 data. When I was entering Jan 1 for 2008, it defaulted to 2007. I had the whole table completed with all the 2008 dates before I realized this. Is there a quick way to highlight all the 2008 data and change only the year?
I want to make a little chart for easy reference that tells me due dates for projects, based on estimated completion times.
I'm already using NETWORKDAYS to find the amount of working days between today and a due date, but I want to flip the formula around, and I'm having trouble getting the syntax right.
For example, column A reads:
0 1 2 3 5 7 10 15 20
Estimated completion times for various projects.
So I want column B to read the date that this would render. A1, value 0, would always produce today's date for B1. B2 would always read one business day into the future, B3 would read as 2 business days into the future, B4 as 3 business days into the future. Does that make sense, and B5 as 5 bd into future.
How can I create a formula to calculate a future date, when it is not a “specific day” in the future? I mean, I have today's date (Jul 15/08) and I need to calculate from that, when it will be the first Monday of November in 2009.
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.
I have a dashboard which has dropdown boxes to pick out the data you want to see. From this data i then have graphs which are graphing 0s from the data making the graph look horrible.
I know I need to add NA() to the cells so that 0s dont graph, but all my data has formulas in and i don't know how to add the NA part to the existing formula.
my formula for each cell is =IFERROR(VLOOKUP($A9,'DB data'!$A$1:$HH$2003,COLUMN(FP1),FALSE),"-")
This looks up name such as "sign on time", then goes to another sheet and retrieves the data.
Where can i add the NA part. I've tried instead of the "-" at the end but doesn't work. and ive tried instead of FALSE and this stops the formula from working.
Sometimes my due dates need to be on the 15th of the month, for which DLL and Donkey gave me: =DATE(YEAR(TODAY()), MONTH(TODAY()) + (DAY(TODAY()) > 15), 15)
This works great except sometimes the 15th is on a Sunday which means I need the result to be the 13th, and sometimes it's on a Saturday which means I need the result to be the 14th. FYI: I am a n00b..
I need to determine a formula which will allow me to calculate a future date based upon a current date with varying time periods.
For example:
I have a bill which is paid on the 15th and last business day of each month. I would like to be able to see the next due date regardless of what day of the week it is.
I have a bill which is paid every other Tuesday. I would like to know the next due date without having to enter +14 for every due date in the future. In other words, it is preferable to be able to open the spreadsheet and automatically see the next due date, not use autofill to repeatedly add +14 to a previous date which would limit the # of future due dates that could be calculated.
I have a bill which is paid on the last business day of each month, not the last Friday of each month. I would need excel to return a value for the last day of the month which = Monday-Friday, regardless of what day of the week it may be as long as it isn't Saturday or Sunday(holiday exclusion would be nice but not required).
I need a formula that will search between dates and time to identify if the person will be present at the conference breakfasts, lunches and dinners.
Therefore the cell needs to look if the current date falls within the arrival and departure date and also if the time within eating times falls outside the arrival and departure times.
I want to put in place a CF icon set of either a green tick or a red cross in cells A1:A20 but when I get to "Manage Rules option box" It is only showing me that I can use "values". Is there a formula that i can use to make it choose the icon set based on text of either "y" or "n" in cells A1:A20 .
I have tried to just change the font to "wingdings2" but other need to use the worksheet and they wont know to use the corresponding text of "O" & "P" .
The following code fills down column B for rows 3 to 110, regardless of the inserted "If Not IsEmply' statement. I've got formulas in Column A from row 3 to 110, but visible values in rows 3-5. I want it to fill the for the visible values only.
I need to send out an order form (spreadsheet) to 100's of people that need to complete the form and email back to me as an attachment. If I was completing the order form myself I would use the "email" icon that I have pinned to my Quick Access Toolbar (QAT). However, most of the recipients don't even know the Toolbar exists.
Is there a way I can insert an icon / hyperlink in the spreadsheet that does the same thing as the QAT icon. I can insert text to say "click here to email your order" (or similar).
I need to keep it in an excel format and an icon is so much better that asking them to save to their hard-drive and attach to an email, etc.
The QAT icon is exactly what is needed but I need to provide a spreadsheet that works for folk who haven't got the icon.
I want to display icon sets in Excel 2007 (arrow) based on prior values. If value is less then previous cell then down arrow should be displayed else up arrow.
a way to hide used rows if the color of the cell in column I is green( color 35 vba) but only if the cell is visible will the macro change the rows visible state, in other words already hidden rows will remain hidden regardless if they match the color requirement.
below is some pseudo code to try to explain better what I'm trying to do. The below code doesn't actually work but I was wondering if someone knew some code that would actually do what I've described.
I need to check a range of cells for 2 different values. If either value is present, I need to shade a cell (outside the original range) red. If neither value is present I need to shade the cell yellow. Can this be accomplished with conditional formatting?
I want to turn a certain cell(sheet1!K2) either green, orange or red depending on certain conditions.There are 3 conditions.If 3 of these conditions are met I want the cell to go red.If two of these are met I want it to also go red.If one of these conditions are met I want it to go orange and if 0 are met I want it to go green.
The conditions are : 1) if a number > 0 is entered into B11:H11 2) if a number > 0 is entered into D11:H11 3) if Ex is entered into cells sheet2!D2:D16
-return red/yellow/green when the value is below/above or within range of targets but dependent on an increasing or decreasing trend.
If the trend is decreasing and the value is above the target is should return red.
If the trend is increasing and the value is above the target is should return green.
For all there is a Threshold.
For example:
Value Target Trend TrafficLight (A) 79% 90% Increasing RED (B) 69% 70%-90% Withing Range Yellow (C) 85% 90% Decreasing Green
Thresholds:
for example (A) - Red: <80%, Yellow: 80%-90%, Green: >90% for example (B) - Red: 60-90%, Yellow: 50-60%;90-95%, Green: <50%;>90% for example (C) - Red: >80%, Yellow: 80-90%, Green: <90%
I have these light blue lines showing up on my sheet. I have "not boarders" selected and they do not print - but I can see them on the screen. Does anyone know how I can get rid of them?
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.
