Array or multiple lookups help

[Gtrain]

Guys,

I am trying to put some error checking into a spreadsheet that people fill out, after completing some work.

Basically i need to make sure that when someone enters values against a location, that they are within a certain range. But the kicker is that there are multiple ranges against any given location and the values cannot cross into a new range even though it is the same location.

I have a attached a make shift spreadsheet to give you an indication of what i mean.

Any thoughts or comments on this would be greatly appreciated.

Thanks

Gareth

[Bob Phillips]

Would values of A, 0.5, 1.0 be okay, but should that reject because the 1.0 is valid for A, but only if the start is >= 0.85?

[rbrhodes]

Hi Gareth,

Messed with it bit. Col I has a list created by Advanced Filter for Unique values. That in turn is used by Data Validation for Loc Name input. Then a sheet change sub checks the input table range. If there is a change in the table it checks that all three cells are inputted and checks the Start/End of the input data...

A little complicated but attempts to check everything!

[Gtrain]

Sorry about the late response new years and all that, but thanks for your comments, they are much appreciated

XLD:- Yes it should reject as the range is outside that section of location A

rbrhodes:- Thanks for your time and effort, this is pretty much exactly what i have been looking to do, i was just unable to come up with a working solution, with a few changes here and there i should be able to come up with a completed solution. I will just have to test now on actual data which has nearly 700 locations, so i will see how it is handled.

I was thinking i might add this as check locations button before the user saves, this way i can add a comment or a list of erroneous cells. So that every time the user enters a new location he is not prompted with an error.

I will let you know how i go.

Thanks again,

Gareth

[rbrhodes]

Hi G,

Yes it's a stab in the dark as I don't know your real data...

It will give a message based on whatever it finds first - start or end that is 'out of range' and that might want to be looked at, but I was trying to outsmart the user and all <grin>

Let me know how it works out. You could even check my rudimentary website or email me (click my sig or username) for more help and/or more about me - I'm always looking for gigs!