# amber

1. ### How to find a range of words in a list of row?

<tbody> Hello All, Here is the sample strings in (A2:A11) result(B2:B11) Lorem ipsum dolor sit amet, amber amber consectetur beige adipiscing elit. amber|beige Nunc azure dictum black odio light blue quis nunc imperdiet viverra. black|light blue|blue Sed at beige beige beige amber...
2. ### Simple "IF" statement for a newbie

I have a sheet which has a list of numbers ranging from -10000 to 10000. I need an IF statement which says if the number is less than -1500 then it should read "red" if it is less than -500 then read "amber" and if it is greater than -500 then should read "green" I can get it do parts but...
3. ### Cnditional Formatting for past dates using ICONS

Good morning I have a 3 year training cycle and have been trying to use the "ICON SET" Red, Amber, Green flags in boxes alongside the date to highlight as follows Red when the training is out of date or past 1065 days from today's date so have tried =TODAY()-1065 in the formula box Amber...
4. ### Nested IF (AND< OR, XOR) Problem

Good Evening, I have a range of cells that are filled with text that is either Green, Amber or Red. If all the cells are Green then the answer is Green, if there is a mix of Amber and Green then the answer is Amber, if all the cells are Amber then the answer is Amber, if the cells are a mix of...
5. ### Conditional Formatting Help

Hi, I'm looking to apply some simple conditions to return a typical Red, Amber & Green (RAG) analysis. The 3 conditions are set out below the table. The problem one is the Amber condition as it has two metrics and this is the one that I struggled to incorporate into a Conditional Formatting...
6. ### Icon Sets against adjacent cells

I would like to use icon sets to display and arrow next to a value if that value is up or down against an adjacent cell. I.e Up if higher, red if lower (amber if level) Also, if possible, I would like to put a variation in where if the value is within 10% of the value of the adjascent cell it...
7. ### Formula to count "blank" learners with reference to other cells

I have currently got a table that refers to a data spreadsheet to count learners, I have got the formula set up when a column contains Green, Amber or Red but I can't figure out what the formula should be if the column is blank. The formula I currently have in J9...
8. ### Help with multiple look ups

Afternoon all Here we go....... I am building a spreadsheet at work for managing the amount of racking damage in the workplace. Racking damage is grade as red, amber and green, red meaning racking needs to be immediately offloaded, amber means it turns to red in 4 weeks then needs offloading...
9. ### conditional formatting with due dates

I need a quick formula to rag status a date that goes amber when it gets to 9 months old and then red when it hits 11 months. requirement is to monitor when a test needs to be competed again. eg. 22/11/18 test taken - so test to be done again before 22/11/19. Date would go amber when 9 months...
10. ### formula help for dates that are in date

Any help will be appreciated if you can please. I have a column of dates, they are yearly courses that people have to undertake. If they don't refresh within the year they are out of date. Anything out of date goes red using this formula =NOW() Anything due to time out within the next 60...
11. ### How to make index match return multiple matches

Hello, I currently have the following formula: =IF(COUNTIF(INDEX(I:I,MATCH((E\$1&("*"&A31&"*)),H:H&J:J,0),),"red"),"red",IF(COUNTIF(INDEX(I:I,MATCH((E\$1&("*"&A31&"*")),H:H&J:J,0),),"amber"),"amber","green")). Basically, I am trying to match cell E1 and A31 with columns H and J, respectively. If...
12. ### Changing Cell Color Based on Cell Value VBA

Hi All, I'm having a difficult time getting this piece of code to work. For a variety of reasons I can't use conditional formatting. My problem: Each cell has a different value: GREEN GREENAMBER AMBERGREEN AMBER AMBERRED REDAMBER RED I simply want something that if the cell says "RED"...
13. ### Too many arguments?

Hi, I'm trying to complete a formula which uses criteria to set a red, amber or green status. The rule I want to create is: > If there are any blanks, show blank > If all selected cells are Yes, show green > If there are any "no" cells, show red > For all else, show amber Here's the formula...
14. ### Using "RANK.EG"

Good afternoon, I work in a call center, and we need to rank our agents using several different metrics. Is it possible to use "RANK.EG" using more than one selecton? Attached is a sample of the worksheet I am attempting to rank. I can rank the top, or the bottom, but not both together. Any...
15. ### Highlight changed values week on week basis

I am trying to compare to sets of data pulled from tool to highlight the changes that Project Managers have made week on week basis as compared to last week. Tab 'old' has last week data while tab 'New' has data. I used formula in conditional formatting "=A1<>Old!A1" but this is only limited if...
16. ### Conditional Formatting

I have 5 columns, headed Q1, Q2, Q3, Q4 and RAG. I need the RAG column to change colour (Red, Amber, Green) based on entries. Q1 will be populated and no action is required in the RAG column. When a figure is entered in Q2, if Q2 = Q1 then RAG will be Amber. If Q2 > Q1 then RAG will be Red. If...
17. ### Red Amber Green Help Conditional Formatting

Hi All, I'm struggling with a query on Excel. I have some cells which i would like to change colour to Red amber and Green depending on where the number falls within a reference table So my information table will automatically change Cell A2, B2, C2, D2 to red, amber or green depending on...
18. ### Conditional formatting of dates

I have a date column in my spreadsheet. I want the date in the cell to go Red if the date has come and gone, amber if the current month and green if the date is ahead. I know there is conditional formatting, however it only allows me to choose next month to go green, and last month to go red...
19. ### Nested IF, COUNTIF and OR statements

I am attempting to build a simple dashboard that will reflect the overall status of several processes with a Red, Amber, Green indicator. For example, I have 6 processes in total, each to be marked as Red, Amber or Green. If all 6 are marked as Green, I want my Overall Status to be marked...
20. ### Using If formula for Rag Status Red Amber and Green

Dear All I need to create a formula where by example using if formula 85% green 81%-84% Amber <80% Red Like =if Cell X 85=>G Or/AND 81%- 84% A OR/AND <81 Red Any ideas? I would use the conditional formatting to do this but my manager would like it as a formula cell. Thank you All

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...