Row # on Another Sheet

Sherry Fox

Hey! First thanks in advance for looking. Is it possible to get the row # on another sheet? I want to know the Row # for New: Release Update Sheet.

New: Release Update Sheet

Release (would require Is Parent to be checked)

Row ID (Auto Number)

Release List (This sheet is where the formula would go)

Releases

Auto (Auto Number)

SoS | Dan Palenchar

This sounds possible but I need some more info to address this.

It sounds like you want your Release List Sheet to show the *Row ID* value for a row in the Release Update Sheet provided the Parent row in the Release List is checked?

I believe you could do this by using COLLECT() to pull in the Row ID values from Release Sheet that match a common criteria (i.e., a lookup value) that is shared across both Sheets (do you have such a value)? With COLLECT() you could limit so that you are pulling in all rows where Release is checked in the parent (might need a helper column to facilitate this) AND the unique identifier is matched.

Something like the below, with the caveat I have not seen your Sheets so can't say for sure this will work!

=INDEX(COLLECT({Row ID from Release Sheet}, {Helper Column in Release Sheet that checks when Parent is checked}, 1, [Unique Identifier Column]@row, {Column w/ matching unique identifiers in Release Sheet}),1)

Dan Palenchar | School of Sheets Solutions Consulting (Smartsheet Aligned Gold Partner)

Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

Smartsheet Tutorial Videos: schoolofsheets.com/youtube

Sherry Fox

@SoS | Dan Palenchar ,

Yes you are correct. And I do have a unique identifier as you mentioned. The Release # consists of primarily numeric characters, although there are times the numeric values are identic, so letters (or words) are added to ensure the values remain unique.

New: Release Update Sheet: Release

Release Sheet: Releases

I entered the formula as follows and below the formula is each component, and which sheet it comes from. I obviously messed up something, as it gives an error of #INCORRECT ARGUMENT SET

=INDEX(COLLECT({Row ID}, {Is Parent}, 1, Releases@row, {Release #}), 1)

Row ID - New: Release Update Sheet

Is Parent - New: Release Update Sheet

Releases@row - Release Sheet (formula is on this sheet)

Release # - New: Release Update Sheet

SoS | Dan Palenchar

This sounds possible but I need some more info to address this.

It sounds like you want your Release List Sheet to show the *Row ID* value for a row in the Release Update Sheet provided the Parent row in the Release List is checked?

I believe you could do this by using COLLECT() to pull in the Row ID values from Release Sheet that match a common criteria (i.e., a lookup value) that is shared across both Sheets (do you have such a value)? With COLLECT() you could limit so that you are pulling in all rows where Release is checked in the parent (might need a helper column to facilitate this) AND the unique identifier is matched.

Something like the below, with the caveat I have not seen your Sheets so can't say for sure this will work!

=INDEX(COLLECT({Row ID from Release Sheet}, {Helper Column in Release Sheet that checks when Parent is checked}, 1, [Unique Identifier Column]@row, {Column w/ matching unique identifiers in Release Sheet}),1)

Dan Palenchar | School of Sheets Solutions Consulting (Smartsheet Aligned Gold Partner)

Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

Smartsheet Tutorial Videos: schoolofsheets.com/youtube

Sherry Fox

@SoS | Dan Palenchar ,

Yes you are correct. And I do have a unique identifier as you mentioned. The Release # consists of primarily numeric characters, although there are times the numeric values are identic, so letters (or words) are added to ensure the values remain unique.

New: Release Update Sheet: Release

Release Sheet: Releases

I entered the formula as follows and below the formula is each component, and which sheet it comes from. I obviously messed up something, as it gives an error of #INCORRECT ARGUMENT SET

=INDEX(COLLECT({Row ID}, {Is Parent}, 1, Releases@row, {Release #}), 1)

Row ID - New: Release Update Sheet

Is Parent - New: Release Update Sheet

Releases@row - Release Sheet (formula is on this sheet)

Release # - New: Release Update Sheet

SoS | Dan Palenchar

@Sherry Fox

I mixed things up when I wrote the formula out (this is the problem of writing formulas by memory!).

I should have written

=INDEX(COLLECT({Row ID from Release Sheet}, {Helper Column in Release Sheet that checks when Parent is checked}, 1, {Column w/ matching unique identifiers in Release Sheet},[Unique Identifier Column]@row),1)

So try:

=INDEX(COLLECT({Row ID}, {Is Parent}, 1, {Release #}, Releases@row), 1)

Sherry Fox

@SoS | Dan Palenchar ,

I entered the formula like you mentioned.

=INDEX(COLLECT({Row ID}, {Is Parent}, 1, {Release #}, Releases@row), 1)

202250 - Row #1872 - Actual Row #1873

2024TBD - Row #2134 - Actual Row #2156

202466 - Row #2031 - Actual Row #2043

I don't understand why it is doing this. These row #s. The row #s I created on the New: Release Update Sheet Row ID column. However, they do NOT match the actual Row # that Smartsheet has by default on the far left. That is the Row # I need.

SoS | Dan Palenchar

Ahh I think I see what is happening, you're encountering a system limitation of sorts.

The Auto Row # system column will, by default, populate with an ascending number every time a new row is created. The same # will never be created twice (unless you reset it, a manual process). If you create 10 rows and they have Row ID from 1-10 the next Row ID will be 11 even if you delete Row ID #10 before. And this holds true so that whenever a row is deleted that Row ID is lost from the list so there will be a gap and thus any row created after the gap will be out of sync with the built in Row #s.

To make your Row IDs match the literal Row numbers, you can perform the following steps to reset the Row IDs:

1. Delete the Row ID column
2. Save the Sheet
3. Add the Row ID column again and add 1 as the starting number (See screenshot)

Unfortunately this will break any formulas that reference this column (there will be a #REF error in the formula text) so you will have to restore the references.

Note that you will have to repeat this process any time a row is deleted. Also, if you move rows around they will be disconnected so it will always have to sorted ascending.

You could likely use the API or a tool like Zapier to populate the actual Row # into a column as well.

_____________________________________________________________________________________________

👨🏼‍💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and/or ❤️Awesome).

🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube

PS - If you have a follow up response attention use @Dan Palenchar so I get notified of your reply!

Sherry Fox

@SoS | Dan Palenchar ,

I have found that the other team is constantly deleting rows and inserting rows. The Analyst in me is having an embolism over that. That is why there is a status, change top Cancelled, don't delete. And all new additions should always be entered at the bottom. But I have ZERO control over that team. There does not seem to be a way that I can capture that row number that is a default setting that appears as the very first column. THAT is the information I need. Why doesn't Smartsheet have that as a field I can access?

SoS | Dan Palenchar

@Sherry Fox

I understand the dilemma, it can get challenging when users do not use the tool as it is designed.

I cannot answer directly for Smartsheet as to why this is not a feature that is available, however, you can submit an enhancement request for this to be added.

There are ways to prevent users from deleting rows but this will be dependent on your and their permissions.

If you want to populate each row with the Row # my team at School of Sheets could develop this for you with the API. You could also use a self service API tool like Zapier as shown below.

Best of luck and feel free to reach out if you'd like to chat.

_____________________________________________________________________________________________

👨🏼‍💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and/or ❤️Awesome).

🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube

PS - If you have a follow up response attention use @Dan Palenchar so I get notified of your reply!

Sherry Fox

@SoS | Dan Palenchar ,

Yes, I have already submitted a feedback request (here's hoping). I will look into Zapier, thanks. And I don't have the ability to prevent that team from inserting/deleting rows, I just have to deal with it. Thanks again for all your help, I appreciate it.

SoS | Dan Palenchar

@Sherry Fox

Happy to help and best of luck!

___________________________________________________________________________________________

👨🏼‍💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and/or ❤️Awesome).

🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube

PS - If you have a follow up response attention use @Dan Palenchar so I get notified of your reply!