Newbie: INDEX and cross-sheet reference.
Hi...and thank you for any help you can offer.
It is quite a transition from Excel and Google Sheets to SmartSheet, but I know it's going to be worth it!
I am trying to make a master sheet and then use different columns of data in other sheets.
I know this works for 1 row:
=INDEX({Symbol}, 1)
I cannot figure out how to change the formula to populate the entire column ("Convert to Column Formula") of 366 symbols from the master list.
I tried @row from this page with no success: https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas
I realize this is probably a super basic question, and I am grateful for the help!
Gregg
Best Answers
-
Of course...thank you for any help you can give me!
The column "Symbol" has 370 stock symbols on the master list.
I hand cut and paste all 370 into Column 4 of the "Shortlist" sheet for reference only.
In the "Symbol" column of the "Shortlist" sheet, I had to change the row # manually, so 1 for row 1, 2 for row 2, 3 for row 3...is there a way for Smartsheet to automatically change the row number for 370 rows?
I think using @row does this...but I can't figure out the syntax.
In Google Sheets, I would use an array formula.
Thank you again!
Gregg
-
Dragfilling is going to be your best option in this particular case.
Answers
-
Are you able to provide a screenshot that shows exactly what you are trying to accomplish?
-
Of course...thank you for any help you can give me!
The column "Symbol" has 370 stock symbols on the master list.
I hand cut and paste all 370 into Column 4 of the "Shortlist" sheet for reference only.
In the "Symbol" column of the "Shortlist" sheet, I had to change the row # manually, so 1 for row 1, 2 for row 2, 3 for row 3...is there a way for Smartsheet to automatically change the row number for 370 rows?
I think using @row does this...but I can't figure out the syntax.
In Google Sheets, I would use an array formula.
Thank you again!
Gregg
-
Try this...
=INDEX({Symbol}, COUNTIFS([Column2]$1:[Column2]@row, OR(@cell = "", @cell <> ""))
-
Perfect, thank you!
I am making this work by dragging the jughandle down...is there a way to have it populate the entire column automatically?
Thank you!
Gregg
-
Dragfilling is going to be your best option in this particular case.
-
-
Thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 143 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
