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?

Find more posts tagged with

Accepted answers

All comments

Julio S.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.