IF & Data Validation - Part 2

[dagindi]

Recently I created a thread requesting help regarding IF & DATA VALIDATION that was quickly solved. While I was using it I ran into a new but different problem along the same topic...

Once again I am attaching the original sample spreadsheet. Please refer to tab 11-23-2010 for the current problem.

B6:B11 have the formula "=UOM" in Data Validation.

C6:C11 has the formula "=IF(B7="Dollar",$C$2,INDIRECT(B7))" in Data Validation.

The issue is if I select "Dollar" in from the list in B6:B11 I still have to select use the drop down list in C6:C11 to get the value in C2.

I tried putting "=IF(B7="","",IF(B7="Dollar",$C$2,INDIRECT(B7)))" in C7 but that works as long as the user doesn't change the option in B7 to something else.

Is there anyway to make the value in C2 automatically appear in the C6:C11 when "Dollar" is selected in B6:B11?

Is there anyway to avoid putting the formula =IF(B7="","",IF(B7="Dollar",$C$2,INDIRECT(B7))) in C7 so that it is a permanent function?

[6StringJazzer]

I saw a similar problem recently. If you have a data validation formula dependent on a cell, and then the value of that cell changes, the content of the cell with data validation will not automatically update to satisfy the condition.

One option is to use VBA to detect the change. For example:

Please Login or Register  to view this content.