IF function with INDIRECT reference for Data Validation source causing issue

[FamilyForce6]

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 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.)

 

[Chrisdontm]

If it is the INDIRECT formula being finicky perhaps you could use the INDIRECT to put the info into another cell, and then set up the Data Validation on the other cells....

Just an idea...

 

[FamilyForce6]

Thanks for your replyChristdontm,
I received an answer similar to yours where I posted in another forum. Just for clarity here's what I did.

Here's what I did to workaround the If formula on the indirect source:
Created a hidden column (H) that had the if formula: If(G8="",$C$4,G8).
then the E8 cell source: INDIRECT(H8).

Works like a charm