Formulas between work sheets

[MiniFav]

Thank you in advance for any help provided.

I am looking to produce a work book with numerous tabs, but for this example I will just focus on two work sheets.

First work sheet
I have columns A,B and C populated across serveral rows.

Second work sheet
I have the same columns however in each row i refer to a cell from the first work sheet.
A1 - =FirstWorkSheet!A1
A2 - =FirstWorkSheet!A2

This is creating a one to one copy across both sheets.

Hoever if i were to insert a row between rows 1 and 2 on the first work sheet, the formulas will update changing =FirstWorkSheet!A2 to become =FirstWorkSheet!A3 leaving a gap where cell A2 on the first work sheet does not get picked up.

So eventually... my question. Id there a way to get this to pick up A2 after being inserted. I am looking only for forumulas rather than VBA.

I have had some success with the below formula.

=IF(ISERROR(INDEX('AD Initial'!$A$2:$A$500,SMALL(IF('AD Initial'!$H$2:$H$500<>"",ROW('AD Initial'!$A$2:$A$500)),ROW(1:1))-1,1)),"",INDEX('AD Initial'!$A$2:$A$500,SMALL(IF('AD Initial'!$H$2:$H$500<>"",ROW('AD Initial'!$H$2:$H$500)),ROW(1:1))-1,1))&""

However pasting this down 1000+ rows causes a lot of slow down due to the arrays.

Again, thank you if anyone has any input at all.

 

[MiniFav]

Just delete all existing cell content below where you put the formula & it will spill down as far as is needed.

Although this works great at first, I have noticed that when i add a row in sheet2 that the formula on sheet one will just skip those new rows and not update.
Would you have any advice to include these new rows?

 

[Fluff]

The formula will ignore any rows where col H is blank, which is what your formula was doing, is that not what you want?

 

[MiniFav]

The formula will ignore any rows where col H is blank, which is what your formula was doing, is that not what you want?

It is all good now, i amended the formula to include blanks and it working like a dream now. Thank you for all your help.

 

[Fluff]

Glad you sorted it & thanks for letting us know.