Creating a New Sheet Referencing Cell Data from Other Sheets

Art Schneiderheinze

In Sheet 1, I have several rows, each with information about a course being developed in a particular term. The sheet has a list of courses, within four parent rows, one for each quarter of the year (e.g., Parent Winter 2019, with several rows as children, one for each course to be developed in that term). 

There is a column in which we enter the name of the master in our learning management system for each course. For example for BUS 123, the master name might be BUS-123-OG-5W-Master.

Once we have the final list of courses in the sheet for the term, we want to automate the creation of the masters in the learning management system. Currently, we have to manually create an Excel file with the required information, including the name we want to call the master.

I would like to create Sheet 2, which will automatically insert the list of the courses in development in a particular term from Sheet 1. Once I have this list in Sheet 2, I can use the master name to reference other cells in Sheet 1 and/or other sheets to populate the rest of the data needed to run the course-creation process in our learning management system. (I would export the Smartsheet as an Excel file, then export the data from Excel as a CSV file.)

I was thinking I could LINK the cells containing the master names from Sheet 1 into Sheet 2. However, there are cases when a course does not have a master name, so if I linked, there would be empty rows in Sheet 2.

Is there a way to "collect" all of the master name from Sheet 1, and then put them into Sheet 2, one on each row.

EXAMPLE:

 

Sheet 1:

In the column, "Master Name":

ROW 1 (Parent): Term (Winter 2019)

ROW 2: Dog

ROW 3: Cat

ROW 4: [BLANK]

ROW 5: Rabbit

ROW 6: [BLANK]

ROW 7: Hamster

 

Then in Sheet 2, could I limit what is pulled by the term (which is in the Parent row)... collect all of the names from the "Master Name" rows, excluding the blank rows, and get something like this:

Sheet 2

ROW 1: Dog

ROW 2: Cat

ROW 3: Rabbit

ROW 4: Hamster

The do not need to be links---I just need that list of names from Sheet 1 (for a particular term, as the names are children of a parent designated as a term) that excludes any cells that are blank.

This would only need to be done once, after we have finalized the list of courses in Sheet 1. Since the  names in Sheet 2 are not linked, I realize it won't maintain consistency with Sheet 1, but that is OK.

 

Thanks!

Shaine Greenwood

Hi—

The Smartsheet Control Center app was designed to automate actions like this and maintain data consistency across the board: https://help.smartsheet.com/articles/2476731-access-smartsheet-control-center

Otherwise, if you have development resources at your organization, you might consider having someone develop the automation that you need with the API: https://smartsheet-platform.github.io/api-docs/