Drop-down lists - circular reference

Hi all,

I wonder if anyone can help me...

I have a spreadsheet (attached) which I am working on to keep account of purchasing expenditure.

I have two columns - 'account code' and 'account description'. I am looking to achieve the following:

Users should be able to type in the account code if they know it, or find the relevant account description (via drop down) instead. So far, I have used VLOOOKUP so that if you type in the code, it will update the description field... HOWEVER... I cannot get it to work the other way round without causing a circular reference.

For example, I want to be able to select the 'Cleaning Materials' account, and excel will automatically populate the relevant account code (113001).

Any assistance would be greatly appreciated!

Cheers,
Martin

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.

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:

Private Sub Worksheet_Change(ByVal Target As Range)

' Formulae used
'    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Setup!RC[-4]:R[100]C[-3],2,FALSE)"
'    ActiveCell.FormulaR1C1 = _
'        "=INDEX(GL_Account_Code,MATCH(RC[1],GL_Account_Description,0))"

' Exit subroutine if changed cell is not in column E or F
If Intersect(Target, Columns("E:F")) Is Nothing Then Exit Sub
' Exit subroutine if changed cell is in heading area
If Target.Row < 4 Then Exit Sub
' Exit subroutine if changed cell is in test area
If Target.Row > 12 Then Exit Sub

' Named Ranges required in formulae below
' GL_Account_Code = Range("B4:B104")
' GL_Account_Description = Range("C4:C104")
' GL_Table = Range("B4:C104")

If Target.Column = 5 Then
    Application.EnableEvents = False
    Target.Offset(0, 1).Value = Application.WorksheetFunction.VLookup( _
                                    Target, _
                                    Sheets("Setup").Range("GL_Table"), _
                                    2, False)
    Application.EnableEvents = True
End If

If Target.Column = 6 Then
    Application.EnableEvents = False
    Target.Offset(0, -1).Value = Application.WorksheetFunction.Index( _
                                    Sheets("Setup").Range("GL_Account_Code"), _
                                        Application.WorksheetFunction.Match( _
                                            Target, _
                                            Sheets("Setup").Range("GL_Account_Description"), _
                                            0))
    Application.EnableEvents = True
End If

End Sub

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

Regards

I hadn't realised that you could use Data Validation in the way described ... that's quite impressive. As pb71 has explained, you need to select a value from one list and then choose the only available option from the second list. Hence, you have to make two selections (albeit, the second is a foregone conclusion). I *think" you will need to delete both entries in order to change corresponding values.

If you do elect to use this approach, I'd recommend using dynamic ranges to eliminate the blank entries in each of the drop down lists. For example, GL_Account_Code would be:

=OFFSET(Setup!$B$2,0,0,COUNTA(Setup!$B:$B)-1)

and GL_Account_Description would be:

=OFFSET(Setup!$C$2,0,0,COUNTA(Setup!$C:$C)-1)

Note that I have also deleted the two blank rows at the top of the SetUp page to make this work.

Using VBA, the second entry will be updated automatically whichever column you choose as the selection. From a user perspective, I think this is easier BUT it does mean you / they need to enable macros ... and not every environment allows this.

Hope this helps

Regards