Simple Index Formula - Invalid Op

Hello. I have a simple index formula just to get one cell down from 9 columns over. I've tried many different options using the Index formula page and am having no luck. The column type the formula is in is the same type as the column I want to pull from (though the destination column is a formula, so maybe that is what causes it not to work?)

Am currently trying this:

=INDEX([Week Due]:[Week Due], 1)

"Week due" is the column I would like to pull from, one cell down. I've tried static options as a test, too, like the one below (like you would do in excel, to count 9 columns over, 2 cells down) and get the same error.

=INDEX([Workstream]134:[Week Due]135, 9, 2)

What is the issue causing the invalid operation error?

Find more posts tagged with

Accepted answers

KPH

Does the Week Due column column have an error in it? If there is an error anywhere in that column, even if not on row 1, the error will be returned.

All comments

KPH

Hi @Audrey512

=INDEX([Week Due]:[Week Due], 1)

Will return the value in the Week Due column on row 1.

=INDEX([Workstream]134:[Week Due]135, 9, 2)

Will return the value in that range on row 9, column 2. Your range is only 2 rows, there is no row 9. If you swap the 9 and the 2 it should work.

But that doesn't sound like it completely solves your question.

Could you add an auto number column to the sheet so that you can use the value in the auto number column to find the row number and therefore the row below?

Create an Auto Number column, I call this ID. This will assign a unique value to each row. It does not matter if the rows are re-ordered, or new rows added above, the ID will be the same. This is fine.
In your formula replace the Row Index part with a MATCH function (shown in bold below - I will explain more shortly).
Add 1 to that number.

=INDEX([Week Due]:[Week Due], MATCH(ID@row , ID:ID, 0) + 1)

The MATCH function is looking for the value in the ID column in the current row, within the ID column, and returns the row number that it appears on. Adding 1 to this gives you the row number for the next row. Like this:

You can hide the ID column so it doesn't confuse anyone.

Hope this helps.

Audrey512

When I do

=INDEX([Week Due]:[Week Due], 1)

I get the invalid operation error, and I cannot figure out why. Despite it being such a simple formula, I cannot figure out what the issue is causing it.

KPH

Does the Week Due column column have an error in it? If there is an error anywhere in that column, even if not on row 1, the error will be returned.

Audrey512

WOW! OK thank you, I found one cell in the sheet that had an error! That fixed it.

KPH

Excellent. Glad I could 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.