Newbie: INDEX and cross-sheet reference.

Gregg Stebben

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

Gregg Stebben

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

012521 8:26 AM.png

Paul Newcome

Dragfilling is going to be your best option in this particular case.

Paul Newcome

Are you able to provide a screenshot that shows exactly what you are trying to accomplish?

Gregg Stebben

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

012521 8:26 AM.png

Paul Newcome

Try this...

=INDEX({Symbol}, COUNTIFS([Column2]$1:[Column2]@row, OR(@cell = "", @cell <> ""))

Gregg Stebben

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

Paul Newcome

Dragfilling is going to be your best option in this particular case.

Gregg Stebben

Gregg Stebben

https://community.smartsheet.com/discussion/comment/276675#Comment_276675

Thank you!

Paul Newcome

Happy to help. 👍️