INDEX, MATCH, Data Validation & Named Ranges in the attached file

[razaas]

The INDEX & MATCH formulas and the Named Ranges and Data Validation in the attached workbook are working well and as I desired. I just want an expert’s opinion and to see if there are better alternatives/improvement chances. Sheet SCMEmpData cell B5 & D5 has the validation lists which are interdependent. Any advice from the experts here is highly appreciated.

[DonkeyOte]

To some extent your options are restricted by the fact your EmpName* Named Ranges are constructed with volatile OFFSET.

What I would say is that the DV set up as you have it is fine given the aforementioned limitations, however, you could simplify your IF based syntax both in the DV source and cell formulae by use of a helper cell, eg:

Please Login or Register  to view this content.

With this number we can then revert the DV formula from:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

If we take the same concept to your cell formula we can shorten:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

In the above - if we change 4 to 3+ROWS(D$6:D6) then we can copy that formula down for all rows (D6:D12)

If the EmpName constructs were non-volatile (using INDEX rather than OFFSET) then in theory we could use an INDIRECT process to really shorten the formulae - either way they would be Volatile.

[razaas]

DonkeyOte,

Thank you so much for the improvement ideas, I was not much satisfied with my IF based syntax. I appreciate your help. Thanks again!