Displaying Returns from another worksheet that change?

[MichaelPK]

Worksheet 1(WS1) has multiple rows and columns. Worksheet 2(WS2) is t
display some of the columns and all of the rows from 1. The issue I hav
is that during my process, some of the rows from A are removed an
placed in another location.
When using =A2 in WS2 the information looks great, the problem come
after removing/deleting row 2 in WS1 and having the remaining rows shif
up. I then get the #REF! error in WS2

 

[MyVeryOwnSelf]

... the problem comes

after removing/deleting row 2 in WS1 and having the remaining rows shift
up. I then get the #REF! error in WS2.

If the rows are placed in another location, it helps if it's done this way:
Select the row.
Use the "cut" operation.
Select the row after the new location for the cut data.
Use the "Insert cut cells" operation.
This way, formulas in WS2 will change to refer to the new locations.

OTOH, if the row is deleted entirely, what result can be expected?

 

[MichaelPK]

;1611405']> ... the problem comes-

after removing/deleting row 2 in WS1 and having the remaining row shift
up. I then get the #REF! error in WS2.-

This way, formulas in WS2 will change to refer to the new locations.

OTOH, if the row is deleted entirely, what result can be expected?

Hi and thank you for your reply.
This will not solve my dilemma, I want WS2 to only refer to WS1 results
The results on WS1 change and I need to see the changed results.

I am dealing with thousands of rows of data, during my process there ar
some of these rows that are identified as ineligible and are removed
The subsequent rows shift up thus creating a new return in A2. Thi
leaves only the eligible rows. the =WS1!A2 in A2 of WS2 changes to #REF
if A2 in WS1 is determined to be ineligible.
I am working with a template that contains multiple tabs, th
determination of eligibility occurs many steps into the process

 

[MyVeryOwnSelf]

I am dealing with thousands of rows of data, during my process there are

some of these rows that are identified as ineligible and are removed.
The subsequent rows shift up thus creating a new return in A2. This
leaves only the eligible rows. the =WS1!A2 in A2 of WS2 changes to #REF!

Here are two possible approaches.

One way: when deleting a row from WS1, select the WS1 tab and all the tabs depending of WS1 (like WS2) and delete the row simultaneously from all of them. This works if the rows of the sheets all line up.

Another way: Instead of "WS1!..." use something line this in WS2:
=INDEX(WS1!$A:$Z,ROW(),COLUMN())
This way, the reference doesn't depend on the history of adding or deleting. If only certain columns are needed, replace "COLUMN()" with the column number of WS1 desired in the column of WS2 where the formula is.

To deal with empty cells, it can be useful to wrap the formula like this
IF(your_formula="", "", your_formula)

Hope this helps.