Styjun

I work with a minority-language team that is translating the Bible into their language. I'm building a Google Sheets document to schedule workflow for our team.

Here is a simplified example I have created for this question:
https://docs.google.com/spreadsheets/d/1ojW-2qh2wDuFbIeojp4zYNPg5mTHmlz8S2OXyufuTaw/edit?usp=sharing
It has commenting access and one could make a copy to edit if desired.

This is the structure of the main sheet ('Plan Complete'):

-- Months are shown along the top
--Books of the Bible down the left
--The translation stage numbers for each book appear underneath 
 the month that they are scheduled. 

This sheet will be updated frequently.

I'm making a new sheet ('Plan Chrono') that shows the same data but organized chronologically. It's structure:

--The months go down the left-hand side 
--Each book and the stage being worked on appears just to the right of the month

Where I am stuck is I don't know how to pull the data from the first sheet into the second sheet and show the data in a chronological format. While I know some basic programming theory and am a lot more capable in making advanced spreadsheets than the average joe, I don't actually know any programming languages.

I have looked extensively into using arrays, CONCATENATE(), MATCH(), INDEX(), VLOOKUP(), COUNTIF(), and even QUERY() but if these are the right tools, I'm not sure how to use them.

I've drawn some inspiration from this other template that uses QUERY() beautifully in 'Calendar View'!A2
https://docs.google.com/spreadsheets/d/1hT_MVbbQWclzDw9kBD6bVXIYFf4KZR8DRVZKKQusqeI/edit?usp=sharing

I have also created intermediate sheets (like 'Prep for Chrono') that gets me part way, but I don't know if they are helping or what to do from there.

One of the challenges is that in a given month we will be working on 1 to 4 books, each in its own stage.

if I need to combine cells, it looks like Q&A will help, but I'm not sure where it fits in:
How to import data from one sheet to "only one cell" in another sheet

I have also looked at these questions but they appear to be for different situations:
Reorganizing Google Sheets data dynamically
Filter data from one sheet to another

I have written some pseudo-code for the 'Plan Chrono' tab, but I don't know how to turn it into working Google Sheets formulas.

This pseudo-code would be placed in 'Plan Chrono' and create a new row for every book stage in each month, generating the whole table. Maybe, using QUERY()?

// Look for contents in the 'Prep for Chrono' tab. For each cell with content, 
// 1. start a new row. 
// 2. in column B, print the year that corresponds with that cell
// 3. in column C, print the month that corresponds with that cell
// 4. in column D, print the quarter that corresponds with that cell
// 5. in column E, print the project year that corresponds with that cell
// 6. in column F, print the book that corresponds with that cell 
// 7. in column G, print the stage that corresponds with that cell
in 'Prep for Chrono'!N8:N10'
 for each cell where ISBLANK=false
 start a new row
 in [current row]B print [same column as cell]6
 in [current row]C print [same column as cell]7
 in [current row]D print [same column as cell]5
 in [current row]E print [same column as cell]4 
 in [current row]F print B[same row as cell]
 in [current row]G print [the content of that cell]

Another approach would be to have just one row per month, and combine books and stages into single cells:

for "Books" column:

// Look for contents in the 'Prep for Chrono' tab. For each cell with content, 
// print the name of the book that corresponds with that cell. 
// Add a return character after printing each cell with content, 
// except after the last one.
in 'Prep for Chrono'!N8:N10'
 for each cell where ISBLANK=false
 print B[same row as cell]&if(not_last_valid_cell(),[return character],"")

for "Stage" column

// Look for contents in the 'Prep for Chrono' tab. For each cell with content, 
// print that cell.
// Add a return character after printing each cell with content, 
// except after the last one.
in 'Prep for Chrono'!N8:N10'
 for each cell where ISBLANK=false
 print the text in that cell&if(not_last_valid_cell(),[return character],"")

In 'Plan Chrono'!I6:N68 I provide two manually coded examples of how I would like the chart to look. The highlighted portion to its left is where I would like the automated page to appear.

I thank anyone who has read this far and especially thank anyone who has some ideas.

This formula, entered into F8, should do the trick for you:

=concatenate(arrayformula(if('Plan Complete'!N8:N67>0, 'Plan Complete'!B8:B67&": "&char(10), "")))

The similar formula for G9 is:

=concatenate(arrayformula(if('Plan Complete'!N8:N67>0, 'Prep for Chrono'!N8:N67&char(10), "")))

The following rows will be the same, except N->O, O->P, etc.

To help you more generally with formatting a system like this, I think the issue you are having is that you don't have a data table anywhere. If I were attempting to simulate a database via Sheets, as you are, I would first create a table or two with all the potential fields [Book], [Expected_Completion_Date], [Languages], etc. then use the query function to manipulate that source of data throughout my spreadsheet. Think of it in terms of data stores/input forms and visualizations/data interfaces.

I hope that helps.