Forum Discussion

Copper Contributor

Feb 23, 2024

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

Formulas and Functions

djclements

Bronze Contributor

Feb 23, 2024

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.

ShaneATV

Copper Contributor

Feb 23, 2024

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.

djclements
Bronze Contributor
Feb 23, 2024
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...