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.

 

[etaf]

=IF( ISEVEN(ROW()),INDIRECT("sheet1!"&ADDRESS(COUNTBLANK($A$1:A1)+1,1)),"")

Book2
	A
1
2	1-A2
3
4	1-A3
5
6	1-A4
7
8	1-A5
9
10	1-A6
11
12	1-A7
13
14	1-A8
15
16	1-A9
17
18	1-A10
Sheet2

Cell Formulas
Range	Formula
A2:A18	A2	=IF( ISEVEN(ROW()),INDIRECT("sheet1!"&ADDRESS(COUNTBLANK($A$1:A1)+1,1)),"")

copying from Sheet1

Book2
	A
2	1-A2
3	1-A3
4	1-A4
5	1-A5
6	1-A6
7	1-A7
8	1-A8
9	1-A9
10	1-A10
11	1-A11
12	1-A12
13	1-A13
14	1-A14
15	1-A15
16	1-A16
17	1-A17
18	1-A18
19	1-A19
20	1-A20
21	1-A21
22	1-A22
Sheet1

 

[MiniFav]

Sorry i may have not been clear before, let me try to explain a bit better.

Below is sheet 1 with some test infomation.

Book1
	A
1	Value
2	TEST
3	TEST
4	TEST
Sheet1

Then next i have sheet 2 which i want to pull in any values from sheet 1.

Book1
	A
1	Value (formula)
2	=Sheet1!A2
3	=Sheet1!A3
4	=Sheet1!A4
Sheet2

Cell Formulas
Range	Formula
A2:A4	A2	=Sheet1!A2

my issues comes when sheet 1 gets a row inserted half way down.
below i have just click row 3, right click and insert.

Book1
	A
1	Value
2	TEST
3
4	TEST
Sheet1

and you see that the formulas adjust but leave out sheet 1 A3

Book1
	A
1	Value (formula)
2	=Sheet1!A2
3	=Sheet1!A4
Sheet2

Cell Formulas
Range	Formula
A2	A2	=Sheet1!A2
A3	A3	=Sheet1!A4

I am looking for a way to have rows inserted but sheet 2 to retain all information without anything falling through the gaps.

 

[Fluff]

How about

=FILTER('AD Initial'!A2:A500,'AD Initial'!H2:H500<>"")

 

[etaf]

=INDIRECT("sheet1!"&ADDRESS(ROW(),1))

 

[MiniFav]

=INDIRECT("sheet1!"&ADDRESS(ROW(),1))

Thank you! this works perfectly. The indirect doesnt seem to cause too much slow down either.

 

[etaf]

you are welcome,
indirect() function is volatile , as i believe ROW is , so the function provided by @Fluff Filter may be better, although i dont know the details of efficiency functions

 

[MiniFav]

How about

=FILTER('AD Initial'!A2:A500,'AD Initial'!H2:H500<>"")

Thank you for the response, however this was giving me a #spill! error.
I would have prefered this option as it would cause less slow down.

 

[Fluff]

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

 

[MiniFav]

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

Perfect! Thank you