Getting #REF when delete paste link 1

[tjonessi]

I don't know how to explain this, but I'll try:

In Excel worksheet1, on first row, we have paste link from worksheet2 of first row, and on second row of worksheet1, we have paste link from worksheet3 on first row, etc.

on worksheet2, we have 4 rows of data, yet only first row is ALWAYS linked to worksheet1. When we delete first row of worksheet2, the second row automatically becomes first row (of worksheet2), then when we go to worksheet1, we get #REF on all columns of that row that was paste link. Is there a way to make worksheet1 maintain first row of other sheets, especially when deleting that row of other sheets?
Thanks.
TJonessi

 

[SkipVought]

Hi,

Using DELETE ROW or INSERT ROW is fraught with hazzards, one of which you have just discovered.

When you DELETE the row on sheet 2, you destroy the reference. Hence the #REF error.

It would be better to use a sequence number column on each sheet and change the sequence number in row 1 to one more than the MAX of that column and then SORT in ascending sequence. THEN delete the LAST row safely.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[tjonessi]

What you suggested works, regarding changing sequence in sheet 2, but they are not in order for any reason, except the author's preference to put in order, so that will not work.

is there a way to use macro to tell it that everytime this row gets deleted on sheet2 to update info on sheet1?

Thanks again.
TJonessi

 

[SkipVought]

Turn on your macroi recorder and record...

Deleting the row

Resetting the reference on sheet1.

Post back with your recorded code and the problem as stated, in Forum707.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]