Newbie: INDEX and crosssheet 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/2481944setformulasforallrowswithcolumnformulas
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
Check out the Formula Handbook template!