Compounding filter functions leaving Errors

[Excelsmallbusinessmom]

I will do the best I can at explaining what I am doing. I am basically creating a decision tree through excel across multiple sheets.

"Sheet 1"!A:J is starting data with a formula in Column J
Sheet 2 copy rows from Sheet 1 where Column C=Yes ....... then there are additional "New" columns that need to be answered with a final criteria/formula in column J
=Filter('Sheet 1'!A:J,'Sheet1'J:J="Yes")
Up to this point the formulas all work. The problem in when I go to sheet 3

Sheet 3 copy rows from sheet 2 where column J=Yes
=Filter('Sheet 2'!A:Z,'Sheet1'Z:Z="Yes")

This results in an error because it can't "See" rows A-J from sheet 1 because they are a formula in sheet 2

Thoughts? Thanks in advance for your support and ideas.

 

[Excelsmallbusinessmom]

I guess the issue is creating a filter on a spill range

 

[RoryA]

Why are you filtering Sheet2 data based on Sheet1, when sheet2's data is already filtered using the same criteria?

 

[Excelsmallbusinessmom]

Why are you filtering Sheet2 data based on Sheet1, when sheet2's data is already filtered using the same criteria?

only “passing” Sheet 1 data is copied to sheet 2. Then in sheet 2 that data is added to and then determined passing or not. If it is passing i want the passing row to go to sheet 3.

 

[RoryA]

You need to use sheet2 data for the filter because the rows in aheet1 are pretty much guaranteed not to match up with sheet2