Autofill Formula causing a problem

ShaneATV 

Sure, I can help you with the issue you're encountering in your Excel spreadsheet where the IF(A6>"",B5,"") formula is causing unexpected changes when editing cells.

The problem arises because the formula in each cell references the cell above it (B5 in this case). When you edit a cell, the formula in the cell below it recalculates, referencing the new value in the edited cell. This chain reaction continues down the entire column, causing all subsequent cells to update.

Here are a few solutions to fix this issue and achieve the desired behavior:

1. Use absolute cell references:

• Instead of B5, use $B$5 in your formula. The dollar signs make the cell reference absolute, meaning it won't change when the formula is copied or dragged down. This will prevent the chain reaction and only update the current cell based on the value in A6.

2. Use the IFERROR function:

• Wrap your existing formula in IFERROR. For example: =IFERROR(IF(A6>"",B5,""),""). This will check if the formula inside IFERROR results in an error. If it does (e.g., when the cell is empty), it will display an empty string (""). This prevents the formula from propagating errors and keeps other cells unaffected.

3. Use VBA:

• If you're comfortable with VBA, you can write a macro that populates the cells with the desired values based on the dropdown selection and prevents further changes. This offers more control and flexibility but requires coding knowledge.

Additional Tips:

• Consider using data validation to restrict the dropdown options to the two choices, ensuring users don't accidentally enter invalid values.
• Explain to users that the auto-populated cell can be edited if needed, but changes won't affect other rows.

By implementing one of these solutions, you can prevent the formula from causing unwanted changes and ensure that only the current cell updates based on the user's input. Choose the method that best suits your technical expertise and preferences.