HAS cross-sheet reference & Invalid Column error

MelodyV

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.

KPH

Hi @MelodyV

Is this another work around you tried to achieve the ticking of multiple boxes on sheet b? So long as the column in sheet A is only allowing one entry from the dropdown I don't think you need HAS. I think IF INDEX MATCH and MAX will do what you need (if I have understood the need correctly):

https://community.smartsheet.com/discussion/113278/cross-sheet-formulas-with-max-date

KPH

Hi @MelodyV

Is this another work around you tried to achieve the ticking of multiple boxes on sheet b? So long as the column in sheet A is only allowing one entry from the dropdown I don't think you need HAS. I think IF INDEX MATCH and MAX will do what you need (if I have understood the need correctly):

https://community.smartsheet.com/discussion/113278/cross-sheet-formulas-with-max-date

MelodyV

KPH, yes this was a workaround I was trying. I saw your response on my older post and that solution did work! Thank you!!

KPH

Great news, well done!