Horizontal Sheets to Vertical Reports

happinessisland

I have several of these sheets that are formatted like this and I want to have the information from each sheet to be put into a report, but instead of the report having the regular column names, I want it to be the ones listed in the Primary column, so Task 1, Task 2, Task 3, Task 4 etc… and then I want what's in the data/tasks column to be listed underneath. and then then the date complete/due date columns will be after each task. Is there a way I can automate this? I know it's a bit wonky:

Jason Tarpinian

No obvious way to pivot the data as your asking, but it can be done. See screenshot and explanation below. First, know that field names are not Dynamic, so you won't be able to automatically have Smartsheet relabel the column the name of the task, you could manually do this, but for the sake of the demo, I just shows them as "FieldN". The first row is also just to make my formulas easier, if you didn't want that, you could manually plug in the numbers to the INDEX formulas.

=INDEX($[Primary Column]:$[Primary Column], [Field1]$1)

You would basically repeat the formulas across the rows as above (the $ makes sure that the field names stay absolute as you drag the formula across). Dates are a little weird, and since you can't format rows, only columns, use this trick to have the dates not appear as #INVALID COLUMN VALUE

=INDEX($[Date Due]:$[Date Due], [Field1]$1) + ""

The limitation here, is that you wouldn't be able to surpass Smartsheet's 400 column limit. So depending on how big this list is going to grow, this may not work. In that case, the answer to your question would be no. Also, if you do it on the same sheet as I've done on my example, deleting/moving rows and columns could be bad, so it might be worth doing the pivoting on a separate helper sheet. This is also going to make your report a bit wonky, as every time you have a new submission, you would need to add that new "FieldN" column to the report.

Jason Tarpinian

No obvious way to pivot the data as your asking, but it can be done. See screenshot and explanation below. First, know that field names are not Dynamic, so you won't be able to automatically have Smartsheet relabel the column the name of the task, you could manually do this, but for the sake of the demo, I just shows them as "FieldN". The first row is also just to make my formulas easier, if you didn't want that, you could manually plug in the numbers to the INDEX formulas.

=INDEX($[Primary Column]:$[Primary Column], [Field1]$1)

You would basically repeat the formulas across the rows as above (the $ makes sure that the field names stay absolute as you drag the formula across). Dates are a little weird, and since you can't format rows, only columns, use this trick to have the dates not appear as #INVALID COLUMN VALUE

=INDEX($[Date Due]:$[Date Due], [Field1]$1) + ""

The limitation here, is that you wouldn't be able to surpass Smartsheet's 400 column limit. So depending on how big this list is going to grow, this may not work. In that case, the answer to your question would be no. Also, if you do it on the same sheet as I've done on my example, deleting/moving rows and columns could be bad, so it might be worth doing the pivoting on a separate helper sheet. This is also going to make your report a bit wonky, as every time you have a new submission, you would need to add that new "FieldN" column to the report.

happinessisland

Thank you @Jason Tarpinian !!!!