Change a cell populated by a dropdown when the value of the source range changes

The only way to do this I know of is through VBA. When you use the drop downs to select a value on the LABOR sheet, the selection is immediately replaced with a formula that gives the same result. This way if you go back and change the Assumptions, the new values flow back to your previous selections on the LABOR sheet.

This has been added to the LABOR sheet code module:

Please Login or Register  to view this content.

Before that would work, however, I had to fix the decimal errors hidden in your ASSUMPTIONS. When you multiply decimal values the results are almost always LONG decimal values. $ values stop at 2 decimal values so your number formatting was hiding the errors. You should always apply rounding to decimal values that have been multiplied to insure the DISPLAYED value is the real value in the cell.

The values look like 10.98 for instance, but instead were actually 10.9833423343 or something like that.

Data validation is a TEXT function, so the Drop Down shows you the text that is displayed in those cells and enters that exact value into the cell.

The way the macro works is it looks at the value just entered, then creates a formula that displays that same "position" from the data validation source list. So it searches the DV range for the value entered.... and wasn't finding them since the DV list had other values formatted to appear like two decimals.

The ROUND() function makes the values IN the cell the same as the values DISPLAYED to the user. Two decimal places.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.