Drop-down lists - circular reference

Originally Posted by pb71

Using your existing named ranges, you could use Data Validation lists. When cell E4 has a value, there will be only one option in the Data Validation list in cell F4 and vice versa. You would have to select the value to populate the cell.

Cell E4 on Expenditure worksheet. Source:

=IF($F$4="",GL_Account_Code,INDEX(GL_Account_Code,MATCH(F4,GL_Account_Description,0)))

Cell F4 on Expenditure worksheet. Source:

=IF($E$4="",GL_Account_Description,INDEX(GL_Account_Description,MATCH(E4,GL_Account_Code,0)))

VBA is another option.

Thanks for that, I've input those formulas

However, I seem to have a hit a problem whereby, whichever cell I select first (code or description) while it now updates the 2nd cell, if I go in to change the 2nd cell - it won't update the original.

For example, I go in and select 121005, it updates the description to 'Transportation Services' but if I was to then change the description drop down to say 'Cleaning Materials, the code would not update?

Originally Posted by TMShucks

You'd have to do this with VBA. You cant have a formula for the Account Code AND the Account Description.

You need a WorkSheet Change Event which will look something like:

You'll need to remove the existing formula but retain both drop down lists.

Regards

Thanks again, I won't be able to try VBA until I am in work as I am currently working on a mac with Office 2008.

A question for both of you, what exactly can the above VBA code do that the Data Validation lists cant?

Just incase I am over complicating things... is there any other way of giving the user the option to either enter in a code or select from a drop down list to give the same result?

I appreciate your assistance so far.