Help with Index & ROW

Hi,
I have a sheet with the Master List of projects, which includes all Fiscal years. This sheet contains Project Year, Location, Start and End Dates, etc… Major metadata associated with the project.

I have another sheet, where I would like to get more granular and tailored for various functions such as Permit Tracking, Close Out Tracking, etc… I want to also filter this by Fiscal Year from the Master List as to have a sheet for those function per Year.

I have been playing around with the INDEX and ROW functions, but I don't seem to get anywhere. I also used the @row but I don't think I am using it correctly.

Here is the formula I am using:
=INDEX({SOURCE SHEET - COLUMN},ROW())

Have also used this formation and NADA:

=INDEX([SOURCE SHEET - COLUMN},@row)

Also used this one and NADA:

=IF(row()<=COUNTIF({SOURCE SHEET - COLUMN},<>""),INDEX({SOURCE SHEET - COLUMN},ROW()),"")

Any help would be much appreciated.

Find more posts tagged with

Accepted answers

All comments

jmyzk_cloudsmart_jp

Hi @Luis Marcelino

In Excel, you can use the ROW() function to get the row number of the cell, as shown in the image below.

Unfortunately, Smartsheet does not have the function.

So, we use the MATCH function to get the row number.

https://help.smartsheet.com/function/match

In the image below, the [MATCH2] column correctly shows the row number with the following formula;

[MATCH2] =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

https://app.smartsheet.com/b/publish?EQBCT=24e131bedc434d1e856b3ef038de633b

In the formula, I used [Row ID], an autonumber column, as a unique value column. If you do not rearrange the rows, as with the third and fifth rows, you can use the [Row ID] as the row number. But, if you move the rows, we use the MATCH function, as shown above.

It is essential to use unique values in the MATCH function. As shown in the MATCH1 column, using non-unique values will result in the same row numbers, such as 1 and 2.'

[MATCH1] =MATCH(Task@row, Task:Task, 0)

Luis Marcelino

Is this something that you can filter within the formula? To provide further details, I have a massive list of projects across multiple years. Those live on a master project list. I am trying to create sheets based on project years, so that we can add additional columns to collect additional information. I don't really want to dirty up the Master Project List with granular details, which is why I want to do it on another sheet where we can narrow down the major project data to City, State, and Project Manager and add additional data at that point.

jmyzk_cloudsmart_jp

Hi @Luis Marcelino

There are a couple of easy ways you can set this up, depending on whether you want the filtering dynamic inside the sheet or fixed in a report:

1. Dynamic filters inside the sheet
You can add Sheet Summary fields (Year#, State#, City#, Project Manager#) and then create simple helper checkboxes directly in your sheet, for example:

https://app.smartsheet.com/b/publish?EQBCT=88d8260e017b49499a5501dee9092afb

[City Filter]   = City@row = City#  

[State Filter]  = State@row = State#  

[Year Filter]   = Year@row = Year#  

[PM Filter]     = [Project Manager]@row = [Project Manager]#  

[State+Year]    = AND(State@row = State#, Year@row = Year#)

Now you can just turn on whichever filter column you need (e.g., City only, or State+Year). By changing the summary field values, the filter columns update automatically. Users don’t have to touch formulas—just pick the dropdowns in the sheet summary and apply the relevant filter.

2. Fixed filters without touching the Master
If you prefer not to add formulas to the Master sheet, use a Report instead.

Set a filter in the report (e.g., State=Co & Year = 2024).
Group/sort as needed.
Duplicate the report and change the filter values for other years/cities/PMs.

This way, you get multiple filtered views of the same Master sheet without cluttering it.

👉 Dynamic = use summary fields + helper checkboxes to switch which filter you want to apply.
👉 Fixed = create reports, duplicate them, and just adjust filter values.

Both approaches keep your Master Project List clean and give you flexibility to slice the data.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.