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.

Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!