Autosort then #REF! issue on another sheet. Lock formulas and mirror cells?

[noveske]

This one feels real stupid. Thought about even just working in a script to copy and paste formulas back in between other scripts.

Sheet 1 - As soon as value are input, the sheet is set to auto update, sort and organize the data. Delete duplicates.
Sheet 2 - Cells reference cells from Sheet 1. Some data is modified by formulas.

=TRIM(LEFT(Input!E2,2))

=IFERROR(RIGHT(SUBSTITUTE(Input!C2," ","|",LEN(Input!C2)-LEN(SUBSTITUTE(Input!C2," ",""))),LEN(SUBSTITUTE(Input!C2," ","|",LEN(Input!C2)-LEN(SUBSTITUTE(Input!C2," ",""))))-FIND("|",SUBSTITUTE(Input!C2," ","|",LEN(Input!C2)-LEN(SUBSTITUTE(Input!C2," ",""))))),0)

When duplicates are deleted, since Sheet 2 just references Sheet 1, the deleted lines just become #REF! errors. Then things get shifted down.

Is there a way so that the cells on Sheet 2 will lock to always reference what's on Sheet 1? Not update or change?
Just need it to mirror without ever moving.

Before	After
=TRIM(LEFT(Input!E2,2))	=TRIM(LEFT(Input!E2,2))
=TRIM(LEFT(Input!E3,2))	=TRIM(LEFT(Input!E3,2))
=TRIM(LEFT(Input!E4,2))	#REF!
=TRIM(LEFT(Input!E5,2))	#REF!
=TRIM(LEFT(Input!E6,2))	=TRIM(LEFT(Input!E4,2))

 

[noveske]

I'm an idiot.

Hope it's not the $.

Nope, that's not it.

 

[Fluff]

Duplicate to: Autosort then #REF! issue on another sheet. Lock formulas and mirror cells?

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.