IF function with INDIRECT reference for Data Validation source causing issue

Hi all,
I've created a spreadsheet for our company's credit card expense reports that has dependent data lists for showing valid account numbers in a drop down (Column E) based on the home department of the employee for whom the report is being completed. The workbook worked great until I revised it this year adding a column that allowed for the "home" department to be over-ridden on any individual line item to use a valid account number from a different department.

I used the following formula in the Data Validation source: "=IF(G8="",INDIRECT($C$4),INDIRECT(G8))" where C4 is the home department and G8 is the Department over-ride.

This works with one exception. When the G8 (Dept Over-ride) is left blank the account number can be filled with any text/data without a validation error. I'm wondering how I fix this to make sure that only approved data is able to be entered in?

(The validation error does work properly when there is data in cell G8.)
Mr. Excel 1.PNG

It doesn't quite work like that, I'm afraid. however, it can be done. One the attached sheet, I have 7 cells G8:G14 - the manual over-ride.

Then (put this where you store all your data for your named ranges), I used (in O2 to O9), a formula to tell Excel which DV list to use:

=IF(G8="",$C$4,G8)

Then the DV formula in the target cells (I used the greenish cells Q2-Q9) becomes simply
=INDIRECT(O2)

and then Bingo... it works

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.