Formula to Auto Update from Sheet with Columns to Sheet with Rows

Patti Petitclerc

We have our publicly published product specifications that I have input into a vertically laid out Smartsheet sheet and I need to have it auto-update the same data I transposed horizontally to rows (in Excel then re-imported into Smartsheet) in another sheet. But I cannot get the row to update when cross-referenced to original column sheet using Index/Match with an identifier.

=INDEX({Nightforce ATACR 1-8x24 F1}, MATCH("Brand", {Row 2 Brand}, 0))

Columns are updated on this original sheet:

I need the updated data from sheet above to auto-update this sheet transposed into rows, so I can run reports off the columns: (I know I can't have a formula in the primary column, so will do a manual Link for that)

Does anyone have any ideas?

Thank you.

Patti Petitclerc

Paul Newcome

I would insert a helper row (for this example I will use the top row) in the horizontal sheet. In this row (Start in the primary column) you want to start with 1 and move to the left ascending.

1.....2.....3.....4.....5.....so on and so forth

until you have the total number of rows you want to pull. You are also going to want a column that I will call "Number" for this example where you do the same starting at the top and going down until you have the total number of columns from the first sheet.

Then your formula would look something like this...

=INDEX({First Nightforce Column all the way to Last Column}, Model$1, Number@row)

Dragfilling this to fill in the rest of the second sheet should give you what you need.

Since you can have a formula in the Primary column, this should fill that in as well.

Patti Petitclerc

Hi Paul,

When you said to number the helper row to the left....did you mean to create more columns to the left of the Primary column? I tried doing this below, referencing all the columns from the original "vertical" sheet, but the formula is showing a circular reference. I'm sure I'm doing something wrong.

=INDEX{Master Product Specifications Range 1}, Model$1, Number@row)

Patti Petitclerc

Hi Paul,

Nevermind, I figured it out! I realized I was putting the formula in the wrong column/cell, Thank you!

Paul Newcome

Yes. The INDEX formula should star tin the cell Model2 and be dragfilled down and right.

Happy to help. 👍️

Slaw

What was the final formula that you used?

Patti Petitclerc

Hi Slaw, here is formula I used;

=INDEX({Master Product Specifications Range 1}, NAME$1, Number@row)