Hi all,

I'll try to explain the issue as much as I can as this is quite complex for me for now.

There are sheets with data. In sheet Account we need to get a connection between External and Internal, so that the first number from External Refers to a certail range from column Internal.

On sheet Flow is the reference table with the different ranges that the first number of column External can referes to. For example in sheet Flow we have values of F99 from 1 to 3, that means in sheets Account values in External that start with 1 to 3 can refer to column Internal again starting from 1 to 3.

However this formula has to adjust for different values for the range. The columns are the same just the data can vary. The range of F99 can be from 1 to 4 or to 5. So the formula has to adjust to the values in each table.

Finally if the value in sheet Account in column Internal is not in the range that is set by sheet Flow, we should get a text to Check the values.

I've attached 2 example files, please ask as many questions as needed.

Thank you in advance.

Validation Macro.xlsm

50_MT0161.xls

Account Sheet

EXTERNAL INTERNAL
110010 100000
110020 100000
110021 100000
110022 100000
110028 101000

FLOW SHEET


EXTERNAL INTERNAL
1***** F99
2***** F99
3***** F99
4***** PL99
5***** PL99
6***** PL99
7***** PL99
8***** PL99
9***** PL99