Tie data to pull into another sheet

Please help

I have a sheet with lots of data. I want to pull that data into a new sheet to go from top to bottom instead of across each column at the top row.

how do i get it to pull each cell horizontally (so pull the whole row) based on its specific data number to then all populate down on the new sheet?

Genevieve P.

Hi @Please help

There isn't a direct way to do this within the core Smartsheet application.

If you want to see the row in a vertical format, one easy way to do this is to double-click on the row. This will open it up in an Edit window that displays the data more like a form.

However if you need to transpose it into another sheet, we could use a formula to do this but you may reach certain cell-linking limits depending on how large your data is.

For example, in your main source sheet, you could add one column noting the Row Number and a top row repeating the current Column Names (or another unique identifier for that column), like so:

Once you have those two reference points, go into your second sheet and set this up in an opposite way, with the column names (or references) written down a column and the row numbers in the top row:

In this sheet, we can use an INDEX(MATCH(MATCH formula to index the entire source sheet and match the Row Number to the Column to bring back the associated cell.

So in column 1, row "Primary" I put:

=INDEX({Full Sheet Range}, MATCH([1]$1, {Row Number Column}, 0), MATCH($Columns@row, {Top Row}, 0))

Meaning I could drag-fill this formula down and it would stay looking at the correct row number and column:

Then I can drag-fill this across as well and the columns will update to look at their own top-row for the row number in the other sheet:

While this formula only uses three {ranges}, the first range is the entire second sheet (or as many columns as you want to transpose) which means this may end up being quite a big reference as your sheet grows.

Does that make sense, and is it what you were looking to do?

Cheers,

Genevieve