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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

into a datavalidtion list Source box on Cell F33

Then in edited the VB code to

Please Login or Register  to view this content.

Any ideas?

Originally Posted by gdallas

I copied and pasted your formula

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.