HAS cross-sheet reference & Invalid Column error


I am trying to use HAS in a cross-sheet reference. I have a drop down form where the user selects a Strategic Priority and it goes into Sheet A below. In Sheet B, I need a formula to pull the text of the most recent selection.

Here is the formula I am using in Sheet B, where Range 1 is the "Latest" column and Range 2 is the "Selected" column.

=(IF(HAS({Selected Strategic Priority Range 1}, "Latest"), {Selected Strategic Priority Range 2}, "False"))

I keep getting the Invalid Column Value error, which an article says means the columns are different types. I get why I can't use the Strategic Priority column in the HAS formula, because it's a drop down, and the place where I'm putting the formula in Sheet B is a text/number column. So that's why I added the "Selected Column" as a workaround -just a simple text/number column that equals the Strategic Priority Column. So I'm not sure why I'm getting the Invalid Column Value error if it's pulling from a text/number column, both references are text/number columns, and the place the formula is going is a text/number column.

Also open to other ways of make this work. I can't use MAX because I need the most recent text, not the date. I don't think I can use Index and Match because there are duplicate values on both sheets. Please help, I am very new to Smartsheet and have spent days trying things, reading articles, and banging my head against the wall. Thank you for any guidance.


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!