Forum Discussion
Autofill Formula causing a problem
I'm making a template form for organisations to fill in and I'm trying to make as little work for them as possible by prefilling as much information as possible or adding formulas to auto-generate data under certain circumstances. While this simple formula works for my purposes I've noticed a problem - IF(A6>"",B5"") - so if A6 is greater than nothing fill this cell with info from cell B5. This works as it should and makes it so that I don't have to prefill 1000 rows with the same info making the form look ugly, and it only fills if something is in that cell, but I noticed that if you are at row 873 and you go back up and change the cell in row 9 because you realised that you made a mistake, everything from row 9 onward now changes to the new value.
So, there's a dropdown box with only two options and most organisations only do one of the two, but the rare organisation does do both, so I can't lock the column so that it can't be edited in case they need to switch between the two and I could just leave it as a dropdown option but I've been asked to make it an auto-generated cell so I need to be able to have it so that it auto-generates and can be altered but when you alter it, it doesn't change everything below the altered cell.
Thank a million $
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.
ShaneATV If I've understood the setup correctly, the formulas in column B will return the value of the cell above it, if the cell in column A is not blank; furthermore, column B has a Data Validation list with two options for "Specialist" and "Regional", which can be used to manually override the formula. Since going back and changing the funding stream in row 9 from say "Regional" to "Specialist" will cause the formulas below it to now say "Specialist", the user would also have to change row 10 back to "Regional", if row 9 was the only row they wanted to change. I don't think there is any other way to handle this scenario with the current setup.
- Yes, you have understood correctly which is good because it was hard trying to explain, and I wasn't sure that I explained it very well lol. Which isn't a huge issue if they change one and realise that it changed all, so they change the next one back to what it was. However, if it's sporadically regional or specialist throughout the document it would be frustrating getting several hundred entries down and changing everything to the one value. So, if there's no work around or alternative formula that might work, I guess dropping the auto-population and just keeping the dropdown box will be the only way.
ShaneATV Yes, the current setup would be prone to user errors, as changing one cell can unintentionally affect others.
Since there are only two options, "Specialist" and "Regional", if one of them is typically used more often than the other, you might also consider modifying the formula slightly to set a "default" option. For example, if "Regional" is used most often, you could try =IF(A6="","","Regional"). This will auto-fill column B with "Regional" by default. The user can then change it to "Specialist" as needed, using the Data Validation list. With this method, changes to one cell would not affect the others. Just a thought...
