Index Multiple date responses into rows
Hello-
I have a form where students are providing three dates they want to work. I want their dates to show up on our calendar so I am trying to turn them into rows using this great response thread; https://community.smartsheet.com/discussion/93956/looking-for-solution-for-forms-to-add-multiple-rows-to-a-sheet
I am able to create multiple rows on my destination sheet, I was able to Index the rows, I just now need to collect the Index out the respective Index dates so it will display and be able to show on my calendar.
My Formula
What I want it to do
However, my Index is returning back as #Incorrect Argument I think because I am using partial column ranges: https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas?source=apphelpicon. I want cell references?
I try to add the [] around my Index but it keeps reverting back to the above formula.
I also tried to run it without the column reference and remove the $1 or $2, thinking I could live without it being a column formula and just drag it down as needed; but no change.
I'm really at a loss of the next step or how to reformulate or why my [index]@row is not being recognized.
Best Answer
-
You can change the column_index of the INDEX function with your Index value, 1,2,3, while setting row_index to 1.
Syntax
INDEX(range, row_index, [column_index])
Formula
=INDEX([Weeknight Date 1]@row:[Weekend Date]@row, 1, Index@row)
https://app.smartsheet.com/b/publish?EQBCT=240001f57b9c4838b83f8bd05e7c701f
Answers
-
You can change the column_index of the INDEX function with your Index value, 1,2,3, while setting row_index to 1.
Syntax
INDEX(range, row_index, [column_index])
Formula
=INDEX([Weeknight Date 1]@row:[Weekend Date]@row, 1, Index@row)
https://app.smartsheet.com/b/publish?EQBCT=240001f57b9c4838b83f8bd05e7c701f
-
Thank you that worked great. I had to add a row ID column to utilize the solution, however it worked great!
-
Happy to help!😁
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives