Validation list & IF / VLOOKUP formula

[tcapewell]

Hello all,

I've been working on a worksheet with some great help from another thread to create a table which using various functions: data validation lists, defined names and IF, VLOOKUP and INDIRECT functions allows my colleagues a way to see which people need to approve Purchase Req's and Purchase Orders. See attached:

The Purchase Requisition chart works perfectly. I have a validation list of countries in C6. A defined name range and INDIRECT validation list in D6 to match the cost centres when a particular country is selected. Then in cells G6-H6 this formula:
=IF(D6="", "", VLOOKUP(D6,PR!B2:F16, 2, 0))
to match the names of approvers given the conditions set in cells C6 / D6

Now my 2 problems are with the Purchase Order chart. An additional condition is the value of the order. In PO worksheet I've had to expand all data to accommodate the new criteria; and my questions are:

1) How can I change the defined name range to match the cost centre lists (defined by country) to cover the now expanded cell range and allowing the PR chart to still work and then change the INDIRECT validation list in D13 to give the cost centre options based on country.

2) Then use a formula in cells G13-H13 to give the results (using IF and VLOOKUP) from PO worksheet when the conditions are set in cells C13 / D13 / E13.

Sorry a little complicated, but I've got so far using all the great info on the forums.

Thanks