Excel formula to Extract Numbers Within a Defined Range

Pending upload of a sample workbook as suggested by TMS, here is a guess. The more I think about it the more of a guess I think this is, but anyway, here goes . . .

Questions:

1. For a particular row, do both the col-A and col-B values need to be within the specified range or just one of cols A and B?
2. If both cols A and B are within range then which number propagates to col-C?
3. Or is there no meaning to be attached to the cells on a particular row and is Col-C just a list of all the numbers from either col-A or col-B that are within the specified range? If so then:

• Can't cols A and B consolidated into a single column?
• What about duplicates in cols A and B? Do they carry over into col-C or is col-C just to have unique values only?
• Does col-C need to be sorted?

Attached is a workbook allowing the MIN and MAX of the range to be propagated to col-C to be changed and is implemented with the following assumptions:

• Col-C is just a list of all the numbers from either col-A or col-B that are within the specified range
• No elimination of duplicates in col-C
• No sorting of col-C

Anywhere close to what you are looking for?

I have attached my query. When you open it the taper button can be changed then all but end of work area is auto filled. I am looking for advice on how I need to use the data from the table (access) as a start point for blank table on the sheet, then I want to add the end of works to the new table. The next bit I want to make a list of numbers between the 2 ie 0.3 to 23.5 and highlight the overhead cable locations that are in that range in the final column. Any ideas

Thanks in advance

A few questions relating to your uploaded workbook:

You have 3 worksheets ("1 lane out", "hard shoulder", "2 lanes out"). Can we focus exclusively on the "1 lane out" worksheet at least to begin with? Or are there inter-dependencies?

Can you confirm that the goal is to populate the table in the upper right of the "1 lane out" worksheet (in the range H3 to J5)?

Am I right in thinking that the intent is to determine whether there are overhead cables between the "Access" point and the "End of works?

If so, then in your data there is only one "End of works" entry (cell B14 = 23.7), the corresponding "Access" is at 19.9. There are cables at 20 (are these all miles?) therefore your table should look as follows:

roadworks.png

If I am right, can you extend your sample with a few more data points - I'm still far from sure that I have enough of the story to provide a full answer.

If I'm on the wrong track then please manually calculate and populate a few rows of your table and explain, in words, how you derived your table entries from the provided data.

On a side track, I happened to notice that you have about 50 separate conditional formatting rules for cell B10 and another 50 for cell B11> I can't glean what you are trying to do, but if you could explain, then I'm sure there is an easier way. This is a secondary issue, so don't bother with it unless it's a quick and easy answer.

I had asked in post #6: "can you extend your sample with a few more data points". Without that I'm still guessing.

If I enter a different End-of-works distance in cell B14, say 36, then do you expect your overhead cable table to now look like:

roadworks_revA.png

You can now change cell-B14 and the Overhead cable table should automatically update.

Is this what you are expecting? If so then is this the complete solution? If not then what else needs doing?

If this is not what you expect then please tell me (at least) how you expect the overhead cable table to look in the event that cell A14 "End of works" is set to 36. Any additional clarifying information would also be welcome.

A couple of details:

1) Why do you have a gap in your Overhead Cable list at H21? I'm going to assume this is a typo and eliminate that gap.

2) Back to the B10 conditional formatting where you have no less than 50 conditional formatting rules for that one cell. One example: 19.2 => red, 19.3 => green, 19.4 => red. Does this make sense? Just checking!

Here's the formulas I applied to the three columns of the Overhead Cable table:
In H4:

Formula:

Please Login or Register  to view this content.

In I4:

Formula:

Please Login or Register  to view this content.

In J4:

Formula:

Please Login or Register  to view this content.

The attached workbook implements the above.

Hopefully we're heading in the right direction.

Thanks that is exactly what i am looking for. The conditional formats are for locations where we cant start work due to location on the motorway,so if red we cant start,or green yes we can,probably an easier way to do it but not for a novice like me haha. I may hide the first two columns so it just shows overhead cable locations next.I think i will have to invest in a book or two and start learning. Thanks again for your patience with me on this

Rather than hide the first 2 columns you could simply delete them. The third column does not rely on them.

Another thought - you could do away with the table entirely and use conditional formatting to highlight the cable distances of interest directly in your list of overhead cables. If you wanted to do that then select H14:H21 then Conditional formatting > Use a formula and enter the following formula: =AND(H14>$B$13, H14<$B$14)


Your D10 conditional formatting (sorry I can't help myself!) If this is something that stays fixed for all time then fine - what you have works. If it needs to change regularly, I imagine it's a pain to update all your CF cases. An alternate approach would be to have a 600 row table, one row for each tenth of a mile. You could then simply put an "x" against each row or range of rows that were unsafe areas. With that approach you could have just one single CF rule for D10 that would look something like: =VLOOKUP($D$10, $M$2:$M$600, 2, FALSE)="x". I can provide more specifics if you need.

That all said, if you are happy that your questions has been resolved then can you please mark the thread as solved, To do this select Thread Tools from the menu link above and mark this thread as SOLVED. If you have additional questions you can start a new thread.

Yes they are fixed and never need to be changed,ha ha i was proud it worked at the time. Thanks for further advice .....i may have a little tinker about.