Row # on Another Sheet

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

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

All comments

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?

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

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

Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

Smartsheet Tutorial Videos: schoolofsheets.com/youtube

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

@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)

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.

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:

Delete the Row ID column
Save the Sheet
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!

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?

@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!

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.