Formula to Auto Update from Sheet with Columns to Sheet with Rows
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
Answers
-
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.
-
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)
-
Hi Paul,
Nevermind, I figured it out! I realized I was putting the formula in the wrong column/cell, Thank you!
-
Yes. The INDEX formula should star tin the cell Model2 and be dragfilled down and right.
Happy to help. 👍️
-
What was the final formula that you used?
-
Hi Slaw, here is formula I used;
=INDEX({Master Product Specifications Range 1}, NAME$1, Number@row)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!