=IFERROR(INDEX(DISTINCT) returning #UNPARSEABLE
Hi Smartsheet Community
I have a rather complicated set up with this one. I have a source sheet with the following columns:
[Full Name], [Month, KES Number], [Classroom], and a helper column ([Month]+[KES Number]+[Classroom]). I'm using the helper column to identify unique instances.
On the target sheet, I have:
[Full Name], [Month], [KES Number] and [Classroom], among many other cells. I would like to populate Full Name, Month, and Classroom for each unique combination of the helper column in the source sheet ([Month]+[KES Number]+[Classroom]).
Because individuals can report for multiple classrooms on a given month, I can't just pre-populate those fields of row data.
The formula that I'm trying to use is:
=IFERROR(INDEX(DISTINCT({PAR Submissions Range 8}:{PAR Submissions Range 8}), {PAR Submissions Range 9}, 1), "")
where:
{Par Submissions Range 8} is the helper column (source sheet)
PAR Submissions Range 9 is an Auto-Number column (1,2,3...) (source sheet)
I have also tried:
=IFERROR(INDEX(DISTINCT({PAR Submissions Range 8}:{PAR Submissions Range 8}), [Auto-Number]@row}, 1), "")
where:
[Auto-Number] is an auto-number column in the target sheet
I keep getting the #UNPARSEABLE error. I'll admit the syntax is getting a little confusing for me here, but I think that this is very possible. Any assistance or advice would be greatly appreciated. Thanks so much, in advance!
Best Answer
-
When using a cross sheet reference, you do not need to have it repeated like you do in that first one.
{Range}:{Range}
is incorrect.
{Range}
is the way you want to go (like you did in the second instance).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
When using a cross sheet reference, you do not need to have it repeated like you do in that first one.
{Range}:{Range}
is incorrect.
{Range}
is the way you want to go (like you did in the second instance).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul,
Thank you for taking the time to respond to my post. Your solution worked perfectly. :)
G.
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K 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
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!