INDEX Woes of a Shifting Source Sheet

Tony Rallo

I am good and stuck.

(ref below image)

The Situation: 

I am feeding values from my customers SmartSheet into the one that my team uses to review their orders.  I have no control over the customer’s sheet, it is barely 2 months old and has 1400 records already so VLOOKUP is not an option (not that their 1st column would be suitable anyway). 

Right now, in conjunction with INDEX, I am using the surrogate key column to pull those values into our sheet for disposition – the shaded cells below. The formula I am using looks something like: =INDEX({ClientSheet Range 1}, $SurrogateKEY@row, 9)

The Problem:

The customer's sheet is fairly stable, finally, but they do occasionally delete or add rows. This causes MyEntries to ‘shift’ in relation to those of the client and be associated with the incorrect ClientEntries. They recently added 3 rows to the top of their sheet and essentially broke my entire sheet.

What can I do here? I feel like I am missing an easy solution.

I hope this makes a bit of sense, its a difficult one to describe. The pasted image is a mock up, the actual data is fairly important and is depended upon.

Thanks You,

TR

Mark Cronk

Hi TR,

It doesn't appear that you have bidirectional links here. Can you have your customer use a copy row automation and copy the row into your sheet when a row is added or changed? If they add or change columns they will just come over and you can adjust your sheet.

Mark

Tony Rallo

Firstly, thank you for the response.

The unidirectional aspect is purposeful.

I do not want our people to be able to change items on their sheet, our customer would probably balk at the idea anyway. Likewise I don't think allowing them access to ours would be a good idea. I for sure do not need (or want) to publish our end of things back to them. No one wants to see how THAT sausage gets made.

Most of the time they only update items on their end, which in turn updates ours. All is good and right in the world. When they add/remove columns it throws our sheet off for sure, but change a few index column ref #s and once again, all good. Its the row insertion /removal that really throws a wrench in things. All our entries below the change are now associated with the wrong inputs from their sheet. IT is not necessarily even obvious.