How do I use Cell reference in a formula when using Index?

Capitalg

I have a simple table that has the following columns (along with other columns),

ZZZ YYY

10 15

and I would like to replace the ZZZ and YYY in the following formula

=INDEX([Thing1]ZZZ:[Thing2]YYY, 1, 1)

With the data from the columns above so that the resulting formula would be:

=INDEX([Thing1]10:[Thing2]15, 1, 1)

Find more posts tagged with

INDEX

MATCH

lookup

Accepted answers

Paul Newcome

TO do that, you will need two helper columns. The first will be an auto-number column (called "Auto" in this example) with no special formatting. The second will be a text/number column (called "Row" in this example) with the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then your INDEX function would change to:

=INDEX(COLLECT([Week Starting]:[Week Starting], Row:Row, @cell >= [Start Row]1), 6)

All comments

Paul.Woodward

Capitalg

Any chance you can explain it a little better or give us the actual cell / column names? Not sure what you are trying to accomplish, my friend! Would love to assist

Paul Newcome

Are you able to provide some screenshots for context?

Capitalg

Sorry, I am new to Smartsheet. I am trying to use the Start Row and End Row columns on row 1 (Start Row = 10, End Row = 15) and use that in the formula in the Date column. I want the INDEX formula to be dynamic and use the Start Row and End Row values instead of hard coding the 10 and 15 as shown below.

=INDEX(WeekStarting10:[Actual Percent]15, 6, 1)

Thanks for your help.

Paul Newcome

=MATCH(Auto@row, Auto:Auto, 0)

Then your INDEX function would change to:

=INDEX(COLLECT([Week Starting]:[Week Starting], Row:Row, @cell >= [Start Row]1), 6)

Capitalg

Thank you for your assistance. I set that up and am getting an #UNPARSEABLE error.

Any addition help is appreciated.

Capitalg

Thank you for all of your help. I misspelled WeekStart - I had a space in there. This works great.

Paul Newcome

Happy to help. 👍️

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.