Pulling single cells from different columns into one column/multiple rows on a new sheet?

Topher Barnett

Hi All,

I have a checklist form that fills in different columns with a status for each test that is passed or failed. Each row is a different room. If a test is failed, there is a 'Notes' column beside the test status column that gets filled in about why a check failed. This all works great.

What I would like to do from there is be able to pull all of the different 'Notes' columns into a punch list sheet that would have each note on a different row. I also want to pull the priority based on the failed state. Copying and pasting would take far too long, as there are 50+ different checks for each room. Below I've done it manually to show how I'd like to to work, but is there a way to automate this process? I thought maybe a workflow, but it only copies entire rows, not individual cells.

Genevieve P.

Hi Topher,

There isn't a way to automate switching columns & rows like that in Smartsheet. The premium app, Pivot App, may be able to re-organize some of your data, but I'm not certain that even the Pivot App could achieve exactly what you're looking for.

Can I ask if it's essential to use a form to track this information? If you'd be willing to have your users fill out a spreadsheet directly, you could set up your main data sheet to look more like your second sheet:

You could either set it up with all of your rooms in one sheet, as I did above, or create one sheet per-room to track this data (you could even set up a template sheet for when you need to create a test check for a new room with all the same checklist items). If you had multiple sheets for each room, you could then create a Report to gather together all of the rows that are either a Fail (normal) or Fail (high) and collect them in one place to review.

If the reason you had a form was because these users don't have Smartsheet, you could always share a Published - Editable version of the sheet for them to fill out.

Let me know if the above set up would work for you. If not, it would be useful to know why you need to track the information in either format.

Thanks!

Genevieve

Topher Barnett

We really liked the form function for the simplicity of filling in information and sharing with people who aren't familiar with the sheet, along with the mobile experience, ability to add specific instructions to the form, and include logic to only show the notes field when there was a fail.

Cheers,

Topher

Genevieve P.

Hi Topher,

That makes sense, there are definitely a lot of benefits to using Forms... however as you've found, forms use columns to populate information and don't currently have the ability to work down rows. This will make it a bit tricker to get to your second view.

I'm trying to think of other ways to pull your data across, however even cross-sheet formulas will want to look at columns, versus rows. You could create cell-links, or use an INDEX(MATCH to bring in certain data, but this would require selecting each of the 50 columns as 50 different cross-sheet references.

What about your second sheet: what's the purpose of bringing it into this format? Could you perhaps use filters or create Reports if you want to adjust the view and limit columns. Or what if you had the Rooms as a drop-down selection, and then used the Card View to display each row as a Card? (You can only have 9 fields showing in a card, but when you double-click on it, it will show the row in a vertical manner).

Callie Barrons

Hi--

I have this same issue for my team: we would prefer to conduct our findings within a form, rather than detail in the grid format.

We note three major accomplishments each week and relate that to our collective objectives and key results. I'd like for ALL accomplishments to push into a single column with the corresponding key result and targets tracked alongside. Ideally, this would push these columns into individual row entries for every notable accomplishment per member of the team.

Has there been any headway in Smartsheet in how this might be possible? Data here is test data for visual purposes.

Thanks!

Screen Shot 2022-02-18 at 1.02.14 PM.png

Genevieve P.

Hi @Callie Barrons

There isn't currently a way to combine those three columns into one column with multiple rows. You could potentially gather all three into one cell, using the JOIN function, but it sounds like you'd prefer them to be split out over multiple, individual rows.

In your scenario, I would suggest having your users fill out the form 3 times into the same columns, versus having one form with three repeating fields/columns.

Cheers,

Genevieve