auto update or resync values in data validated lists

Hi,

I was wondering if there was some way to auto update or resync values in data validated lists which are dependant on information in other cells.

I attach my spreadsheet. My list in Cell F32 changes depending on what has been selected in cell F31. However, my problem is that, until you select cell F32, it still keeps the previous value on display, if you know what I mean. So! What I would like to happen, is when I select a different option in cell F31, then I would like cell F32 to update and show the first available option from the new list?

This is the non-programming forum, so I presume you are looking for a worksheet function solution.

Well, in short, you can't get what you want indefinitely without VBA. You can get it once, though. Put this formula into F32:

=INDEX(INDIRECT(VLOOKUP(F31, table4d1a, 2, 0)),1)

This will show the first item for the list based on the choice in F31. However, as soon as you use the drop down in F32 to make an alternate choice, the formula will be gone.

To be able to change F31 over and over, use F32, then change F31 again, you would need to add VBA. Let me know if you're open to that.

Ok, here you go:

1) Right-click on the Motor Calcs (Generic) sheet tab
2) Select VIEW CODE
3) Paste this macro into the sheet module that appears:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$31" Then _
    Range("F32").FormulaR1C1 = "=INDEX(INDIRECT(VLOOKUP(R[-1]C, table4d1a, 2, 0)),1)"
End Sub

4) Close the editor and save your workbook.

Now use your sheet normally. Anytime you make a change to cell F31, F32 will get that formula I gave you put back in.

I don't understand your final question.

Formula for I31:

=IF(LEFT(F31,5)="Cable", INDEX(INDIRECT(VLOOKUP(F32, table4d1aconfig, 2, 0)),1),"Not Applicable")

Code adjusted to reapply the I31 formula when you change F31:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$F$31" Then
    Range("F32").FormulaR1C1 = "=INDEX(INDIRECT(VLOOKUP(R[-1]C, table4d1a, 2, 0)),1)"
    Range("I31").FormulaR1C1 = "=IF(LEFT(RC6,5)=""Cable"", INDEX(INDIRECT(VLOOKUP(R[1]C6, table4d1aconfig, 2, 0)),1),""Not Applicable"")"
ElseIf Target.Address = "$F$32" Then
    Range("I31").FormulaR1C1 = "=IF(LEFT(RC6,5)=""Cable"", INDEX(INDIRECT(VLOOKUP(R[1]C6, table4d1aconfig, 2, 0)),1),""Not Applicable"")"
End If

End Sub

Not quite getting that too work mate. I done a bit of house keeping on the Motor Calcs (Generic) sheet too, and I think I have revised your code appropriately, but still unable to get it to work correctly.

Sheet attached

What i did:

I copied and pasted your formula

=IF(LEFT(F31,5)="Cable", INDEX(INDIRECT(VLOOKUP(F32, table4d1aconfig, 2, 0)),1),"Not Applicable")

into a datavalidtion list Source box on Cell F33

Then in edited the VB code to

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$F$31" Then
    Range("F32").FormulaR1C1 = "=INDEX(INDIRECT(VLOOKUP(R[-1]C, table4d1a, 2, 0)),1)"
    Range("F33").FormulaR1C1 = "=IF(LEFT(RC6,5)=""Cable"", INDEX(INDIRECT(VLOOKUP(R[1]C6, table4d1aconfig, 2, 0)),1),""Not Applicable"")"
ElseIf Target.Address = "$F$32" Then
    Range("F33").FormulaR1C1 = "=IF(LEFT(RC6,5)=""Cable"", INDEX(INDIRECT(VLOOKUP(R[1]C6, table4d1aconfig, 2, 0)),1),""Not Applicable"")"
End If

End Sub

Any ideas?

Originally Posted by gdallas

I copied and pasted your formula

=IF(LEFT(F31,5)="Cable", INDEX(INDIRECT(VLOOKUP(F32, table4d1aconfig, 2, 0)),1),"Not Applicable")

into a datavalidtion list Source box on Cell F33

That wasn't a DV formula, it was a worksheet formula. The DV formula for F33 would be:

=IF(LEFT(F31,5)="Cable", INDIRECT(VLOOKUP(F32, table4d1aconfig, 2, 0)), notapplicable)

Then I edited the VB code to...

No, here you go, we need to make sure the formula being inserted has corrected cell values...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$F$31" Then
    Range("F32").FormulaR1C1 = "=INDEX(INDIRECT(VLOOKUP(R31C, table4d1a, 2, 0)),1)"
    Range("F33").FormulaR1C1 = "=IF(LEFT(R31C6,5)=""Cable"", INDEX(INDIRECT(VLOOKUP(R32C6, table4d1aconfig, 2, 0)),1),""Not Applicable"")"
ElseIf Target.Address = "$F$32" Then
    Range("F33").FormulaR1C1 = "=IF(LEFT(R31C6,5)=""Cable"", INDEX(INDIRECT(VLOOKUP(R32C6, table4d1aconfig, 2, 0)),1),""Not Applicable"")"
End If

End Sub