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.
Answers
-
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())
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
-
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))?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!