Formulas

Copper Contributor
Hi
I am new to excel as I have recently started a new job so this may be a simple one to answer. I need to create a table which will have 3 columns, START OF WORK AREA, END OF WORK AREA, AND OVERHEAD CABLES. I want the table to highlight dangerous work areas inbetween two locations. So say if I input 4.0 under START OF WORK AREA,6.0 under END OF WORK AREA the OVERHEAD CABLES column will list all areas with the cables in. I thought of conditional format but I can't highlight certain numbers that are between the range.
Thanks in advance
15 Replies
In your example, what will the Overhead Cables column list? Please elaborate.
I was hoping it would list the locations of the overhead cables highlighted in a colour e.g work area 4.0 to end 8.0, overhead cable 7.6 this in red?.
Thanks for your response
Given the start and end of the work areas, how do you determine the work areas between them and which work area is dangerous?
The work are is the whole area between start and end. And I wanted the condition set so if any of the dangerous locations are inbetween the range they will come up in separate column highlighted in red. The work location will be changed daily so all the dangerous locations I will need in the formula or conditional format so as the work area changes only the dangerous locations in the new range are highlighted.
It seems you already have a given list of dangerous locations and you want to highlight such dangerous locations that are between the start and end work areas. Is that what you want to achieve?
Yes that's what I require. Just not sure how. Thanks
Now it’s clear that dangerous work areas are pre-determined. How about work areas between start and end? How are they determined? At what increments are intermediate work areas determined? At increments of 0.1, such that 9 work areas are between 4.0 and 5.0? I hope so.
The work area could be the whole area between say 0.4 and 4.2 and in this area there may be only one or as many as 4 or 5 areas of danger. So in the whole work area there could be overhead cables at 0.6 and 4.1 so these 2 figures would be displayed in red in column 3.
Do you also have a pre-determined list of work areas between the start and end?
The work area can range from 0.0 to 65.0. Any where inbetween
Let me succinctly state your aim this way:
You want to highlight in red fill color the dangerous work areas that are between the start and end work areas. Thus, you need 2 lists:
1. List of dangerous work areas, which you admitted is pre-determined and so you have no problem with that; and
2. List of work areas between the start and end, wherein my last question was anchored.
If list 2 is also pre-determined, you just have to determine the instances of list 1 in list 2 and highlight those instances in red fill color.
Back to my question, such that list 2 is not pre-determined. In such case, you have to determine it and you have to know how? Perhaps at increments of 0.1 from start.
Without list 2, there is nothing to which you can compare list 1. Consequently, there would be nothing to highlight.
Yes they would be in increments of 0.1 from the start.

In the attached file, the danger areas that are within the latest work area are highlighted with red fill color. 

That's brilliant. Looking at that formula I would never of stood a chance. Thanks for your help it's much appreciated. I will get to work on it now.
Welcome and good luck on your new job.