Adjacent cell formulas conflicting?

I'm populating a sheet with data from two similar but different sheets. Very simple

Column one is a number, entered by a user. Column two and three add a prefix "MTA-" this is to make column three conform to the "row ID" of the reference sheets. Column four is a simple INDEX/MATCH:

=INDEX({Sheet - 2024 data range}, MATCH([Row ID]@row , {Sheet - 2024 data range}, 0))

Easy right? And it works fine, until I add column five, same thing but the 2025 data.

=INDEX({Sheet - 2025 data range}, MATCH([Row ID]@row , {Sheet - 2025 data range}, 0))

Because then column 4 starts to reference 2025 data or drops a reference completely and I get an error. I think I have another way to approach this, use SUBSTITUTE to remove the prefix in the source sheets and INDEX/Match against that, but that presupposing that the addition is causing problems. Bottom line I would like the user to enter a number (row ID without the alpha prefix) and look at the two sheets to return the contents of a cell (Created by). Two columns are fine but they keep getting mixed up, I don't want a huge nested formula for something so simple.

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

Hey @Pete Spin

When you added the formula to Column5, did you copy/paste Column4 formula into Column5, then simply edit the formula and change 2024 to 2025 and then change which column the reference pointed to?

Go back to Column 4, and open the formula. Copy the exact name of the 2024 reference (so you can use the exact reference name in your sheet). Delete the 2024 reference from the INDEX function. After deleting the one reference, re-create the reference to the other sheet, pasting the name back as the reference name. Make sure this is referencing the correct 2024 column in the other sheet. If necessary, do the same thing for the 2025 reference - but make sure you delete it first then recreate.

Does this fix your issue?
Kelly

Pete Spin

Thanks Kelly,

I actually tried it a couple of ways; I copied a generic formula from my cheat sheet into Column 4, then updated the references. That worked as expected so I copied that into 5 and updated those references.

Then once the problems arose, and updating the references and reference names didn't work, I deleted the columns and built them one at a time. I did notice the "Sheet reference name" was mixed up and tried to fix those (it's the first time I've even paid attention to that), that was no help. I went back and forth a few times before having to put out other fires. I suspect the "sheet reference name" might be to blame, also since the formula is so simple I should just build it from scratch.

Let me try those..

Paul Newcome

Don't update the references when you enter the second formula. Remove the references from the formula and replace them with brand new cross sheet references to the second reference sheet.

Pete Spin

Paul,

Yes tried that as well, I would delete the reference "{sheet-reference range x}" and enter a new reference. It seems to be stable now, I simply typed in the formula from scratch adding the references as the formula helper suggested.

Pete Spin

Well, this was unexpected.. other than the column properties everything is gone…

Pete Spin

However it does work… I cannot see the cell formulas, but once data is entered into the Primary column the sheet actual populates and functions.. but anyone else would think this is a blank sheet..

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.