Cross-sheet linked values formula is not working. Help please.

Hi. I am by no means an expert, and I'd appreciate your help to solve what looks like a bug, but it may be my error.

I want to copy values from a column in one sheet to another sheet. For ease, I will call these sheets "Origin" and "Destination".

For example, I want the value in row 1 of a "Origin" to be the same value in row 1 of "Destination". Then I want the value in row 2 of "Origin" to be the value of row 2 in "Destination", and so on. I have 25 rows in "Origin".

In "Destination", I created a column that numbers the rows. The top row is "=1". Then under that I created a formula that adds 1 to the cell above, so I end up with numbers from 1 to 25.

I then created a new column in "Destination", where my data needs to be. I placed this formula in that column:

=INDEX({Project name}, RowCounter@row)

{Project name} references the column in "Origin" where my values are.

What is happening now is that my formula works almost, but for some reason that I cannot figure out, it skips some rows. The first cell in "Destination" correctly displays the first cell in "Origin". Then for some reason, the next row in "Destination", row 2, does not show the value from row 2 in "Origin". Instead, it skips to row 3. After that it works for several rows, and then some rows later, the formula again skips one value. This happens again later, with no pattern that I can see.

I can't make heads or tails of what is happening. Help please.

Find more posts tagged with

Accepted answers

All comments

SoS | Dan Palenchar

Hello @Carlos1595

First I would recommend replacing your number counting column with an Auto (Number) column. It automatically adds a number to each row but it also has "memory" so no row will ever have the same value (e.g., if you delete a row).

For your lookup formula use INDEX(MATCH())

Carlos1595

Thank you, I didn't know about Autonumber.

I'm not sure I follow on why I would use INDEX(MATCH()). I thought the solution I came up with should work, except it seems to have some bug. But I'm happy to change tactics. I tried INDEX(MATCH()) but it seems more convoluted.

What eventually I want to do is this:

IF (value in Column A of "Origin" = "Red Flag") THEN Copy value from Column B in "Origin" to "Destination"

How would that work with INDEX(MATCH))?

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.