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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives