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.