Creating a New Sheet Referencing Cell Data from Other Sheets
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!
Comments
-
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/
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!