Formula helper column not updating dropdown values correctly

Terri1019

Hello. I have a "Responsible-report" helper column that captures a value from a field in the "Responsible" column and flows up to all the ancestors.

=Responsible@row + CHAR(10) + JOIN(DESCENDANTS(Responsible@row), CHAR(10))

I updated the menu dropdown options in the "Responsible" column and the fields that were "incorrect" because of the update. However, the "Responsible-report" column still returns a few incorrect values. I double-checked the "Responsible" column in the sheet and there is not a field that has an incorrect value in it. I also took the column formula off "Responsible-report", deleted all of the field values and then added it back in and it is still pulling the incorrect values. Any ideas of why this is happening and how to fix it? Thank you!!

The "Responsible" column is a dropdown list, does not allow multiple values per cell and does restrict to list values only.

The "Responsible-report" helper column is Column Type-Dropdown list, does allow multiple values per cell and does restrict to list values only.

See attached PDF for examples. Thank you!

Untitled document.pdf

Georgie

Hi @Terri1019,

Having reviewed your screenshots and tested on my end, it appears that the incorrect values are still present in the “Responsible-report” column in the source sheet - either within cells in that column, or listed as values in the column properties.

When filters are created or edited and they use the criteria “has any of”, the only values you can select are any values present within cells in the column and/or any values listed as options for the dropdown column. Since your screenshot showing the sheet filter criteria shows that the incorrect values are still available for selection, it follows that these values are still present in the sheet.

My suggestion would therefore be to do the following in the source sheet:

1. Remove all incorrect values from the values list in the column properties for the “Responsible-report” column.
2. Remove the formula completely from the “Responsible-report” column and then add it back in:
   1. Convert the formula to a cell formula.
   2. Remove the formula from all cells in the column (click the column header and hit backspace).
   3. Save the sheet.
   4. Enter the formula back into any cell in the “Responsible-report” column and convert it to a column formula.

Hope that helps! 

Georgie