Return multiple rows of results based on one row elsewhere
I'm trying to convert collected information on team's schedules in office into a calendar view to represent it.
The data is collected as 1 row per week (checkmark if in the office of Week Start on X Monday).
However, converting that into 1 row per day each Team is in the office (to be able to represent in the calendar view) is proving challenging.
I can calculate the dates as references to the Week Start (adding 0,1,2,3,4 to the result if the cell is checked off) =IF(T:T=1,(Week Start)+1)
I think I'm supposed to be using JOIN, COLLECT and probably MAX or min with a unique number added to it because I'm dealing with duplicate names to pull on in Team Name, but I have no idea how to convert the horizontal row data into a unique row result.
I'd love the data to look like this ^ and to result in a calendar that looks like below.
Any thoughts on how to go about that?
Answers
-
Hi @Adrienne Van Halem ,
My advice to achieve what you intend would be to insert child rows to each week start and leave that blank as the parent row. Then check each box for each day individually as shown below.
This will allow you to calculate the "Date" values with the following unique formula: =IF(M@row = 1, PARENT([Week Start]@row), IF(T@row = 1, PARENT([Week Start]@row) + 1, IF(W@row = 1, PARENT([Week Start]@row) + 2, IF(Th@row = 1, PARENT([Week Start]@row) + 3, IF(F@row = 1, PARENT([Week Start]@row) + 4))))) in the "Date" Column that you can make a Column formula so that values are calculated automatically for each row.
I hope that this can be of help.
Cheers!
Julio
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!