Consolidating several smart sheets in one (appending columns)
Hi. I have several smart sheets with 2 columns, being the first column common to all. What is required is to have an aggregated smartsheet with the common first column and append the second columns of the other smart sheets.
I.E
Smarthsheet 1
Subject Juan
math A
physics B
Smartsheet 2
Subject Peter
math A-
physics C
Aggregated Smartsheet
Subject Juan Peter
math A A-
physics B C
I have tried using reports but it added as new rows.
Thanks.
Comments
-
Hi Fernando,
Depending on how many records you have you could use Cell-linking. Another option would be to use cross-sheet formulas.
How many rows/cells are there in one sheet? Are the records on the same rows in the different sheets? (cell-linking won't work otherwise)
The sheets would be linked/collected on another sheet.
Would that work for you?
I hope this helps you!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
You could use an INDEX/MATCH with x-sheet references. I would need some screenshots and/or more details to be able to give you something more specific though.
-
Hello Paul. Each Sheet have 4 columns. The first 3 are common, so, should be populated only once in the aggregated sheet. The number of rows of each sheet is about 1700 lines.
The forth column of each sheet, which is the one required to be populated in the aggregated sheet, will contain a user in the header and alphanumeric values in the rest of the cells.
Is pretty much what I have posted in the first question, but with a larger number of rows (about 1700).
Thanks!
Fernando.
-
More information. The total number of sheets is about 180. So, the aggregated sheet should have 3+180 columns with about 1700 rows.
-
Hello Andrée
Each Sheet have 4 columns. The first 3 are common, so, should be populated only once in the aggregated sheet. The number of rows of each sheet is about 1700 lines.
The forth column of each sheet, which is the one required to be populated in the aggregated sheet, will contain a user in the header and alphanumeric values in the rest of the cells.
Is pretty much what I have posted in the first question, but with a larger number of rows (about 1700).
The total number of sheets is around 180, so the final aggregated sheet would have 3 (common) + 180 rows, and 1700 lines.
Thanks!
Fernando.
-
Is each sheet an individual name with various subjects? What kind of data do you have where?
-
It sounds like you are trying to transpose the data. The Smartsheet is limited to 200 columns.
It seems you would be better off having the subjects (math, physics) as columns and the students (Juan, Peter) as rows.
Why do you have 180 sheets?
This seems like it requires a redesign.
What am I missing?
Craig
-
Happy to help!
I agree with Craig's comment. How is your set up structured?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I've been thinking the same thing about having the subjects as columns and the students as rows, but apparently each sheet already has 1700 rows in it.
That's why I'm trying to figure out exactly what kind of data is being populated where and why.
Screenshots would be SUPER helpful.
-
Hi all. Each sheet will belong to a resource of my team. What I'm trying to do is to have their auto evaluation in this 1700 categories. I know it sounds too much but there are 1700 categories (subjects) defined in the company and I should attain to it.
So the idea is to create one smartsheet per user and make each user editor of his sheet. With this they will be able not to only fill but to update once they gather a new knowledge or certification.
Finally the idea is to have all this info in only one sheet, where I can look for a category (subject) and know who in the team has the required level of kwnowledge.
I have transpose each sheet and run a report. Seems to be close, but the problem now is that I have the subjects duplicated. Even so, having 180 columns at max will not work in my case.
Report
Subject math physics
Juan A B
Subject math physics
Peter A- C
I should be able to have the subject row only once.
Thanks.
Fernando. -
The only thing I can think of at this point would be to put each person across the columns starting at column2. In row one use a "JOIN(COLLECT(IF(ISTEXT" kind of thing to make one long string of each certification per person. In Column 1 starting in row 2, put your list of 1700 certifications. You can then use checkbox columns with an IF(FIND(Certification@row, [Current Column Name]$1) > 0, 1) to search the collective list of certificates for the person in that column and check the box if the certification in that row found in that person's list at the top of the column. Transposing of the actual grades would have to be manually done due to the x-sheet reference limitations.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives