Formulas between work sheets

MiniFav

Board Regular
Joined
Mar 10, 2020
Messages
81
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
=IF( ISEVEN(ROW()),INDIRECT("sheet1!"&ADDRESS(COUNTBLANK($A$1:A1)+1,1)),"")

Book2
A
1
21-A2
3 
41-A3
5 
61-A4
7 
81-A5
9 
101-A6
11 
121-A7
13 
141-A8
15 
161-A9
17 
181-A10
Sheet2
Cell Formulas
RangeFormula
A2:A18A2=IF( ISEVEN(ROW()),INDIRECT("sheet1!"&ADDRESS(COUNTBLANK($A$1:A1)+1,1)),"")


copying from Sheet1
Book2
A
21-A2
31-A3
41-A4
51-A5
61-A6
71-A7
81-A8
91-A9
101-A10
111-A11
121-A12
131-A13
141-A14
151-A15
161-A16
171-A17
181-A18
191-A19
201-A20
211-A21
221-A22
Sheet1
 
Upvote 0
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
1Value
2TEST
3TEST
4TEST
Sheet1


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

Book1
A
1Value (formula)
2=Sheet1!A2
3=Sheet1!A3
4=Sheet1!A4
Sheet2
Cell Formulas
RangeFormula
A2:A4A2=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
1Value
2TEST
3
4TEST
Sheet1


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

Book1
A
1Value (formula)
2=Sheet1!A2
3=Sheet1!A4
Sheet2
Cell Formulas
RangeFormula
A2A2=Sheet1!A2
A3A3=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.
 
Upvote 0
How about
Excel Formula:
=FILTER('AD Initial'!A2:A500,'AD Initial'!H2:H500<>"")
 
Upvote 0
Solution
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
 
Upvote 0
How about
Excel Formula:
=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.
 
Upvote 0
Just delete all existing cell content below where you put the formula & it will spill down as far as is needed.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top