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
-
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
Answers
-
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, yes this was a workaround I was trying. I saw your response on my older post and that solution did work! Thank you!!
-
Great news, well done!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!