Using SortBy and Filter (or similar) together with Structured References.

[G-BullMetal]

I have a column of data [CNC Order] in a table called MasterSchedule. The column is blank except for seven random rows with the numbers 1-7 in them. 
The goal is to making a 'breakout' table in another workbook that only shows the rows with a "CNC Order" number, sorted from 1-7. 

=SORTBY(OriginalWorkBook.xlsx!MasterSchedule[CNC Order], OriginalWorkBook.xlsx!MasterSchedule[CNC Order])

Gives me back 1-7 and then a bunch more rows with zeros, i.e:
1

2

3

4

5

6

7

0

0

0

0

0

....


However

=FILTER(SORTBY(OriginalWorkBook.xlsx!MasterSchedule[CNC Order], OriginalWorkBook.xlsx!MasterSchedule[CNC Order]), OriginalWorkBook.xlsx!MasterSchedule[CNC Order] > 0)

and other variants just give me 

0

0

0

0

0

0

0

OR

7 empty cells.

Switching the order like "SORTBY(FILTER( or SORT(FILTER(" just gives me #VALUE

How can I use filter properly in this circumstance, so I only get my 7 rows.
Btw it'd be fine i guess to get all the columns with #Data instead of just specific ones but I am choosing specific row by row to intentionally hide certain columns

[#2]

@G-BullMetal To address the scenario, as presented, try either of the following:

=LET(
   arr, SORTBY(OriginalWorkbook.xlsx!MasterSchedule[CNC Order], OriginalWorkbook.xlsx!MasterSchedule[CNC Order]),
   FILTER(arr, arr > 0)
)

- OR -

=SORT(FILTER(OriginalWorkbook.xlsx!MasterSchedule[CNC Order], OriginalWorkbook.xlsx!MasterSchedule[CNC Order] > 0))

Having said that, it seems like you may want to include additional columns in the final output. If so, try something along these lines with the CHOOSECOLS function...

=LET(
   arr, SORTBY(OriginalWorkbook.xlsx!MasterSchedule[#Data], OriginalWorkbook.xlsx!MasterSchedule[CNC Order]),
   CHOOSECOLS(FILTER(arr, CHOOSECOLS(arr, 2) > 0), 1, 3)
)

-- OR --

=CHOOSECOLS(SORT(FILTER(OriginalWorkbook.xlsx!MasterSchedule[#Data], OriginalWorkbook.xlsx!MasterSchedule[CNC Order] > 0), 2), 1, 3)

Note: in these examples, I assumed the [CNC Order] column was column 2, and I chose to return columns 1 and 3 only. Adjust the column numbers accordingly to meet your needs.

Also, please be aware, Excel has limited support for dynamic arrays between workbooks, and this scenario is only supported when both workbooks are open. If you close the source workbook, any linked dynamic array formulas will return a #REF! error when they are refreshed.

[#3]

Great i'll give these a shot. Didn't know about LET() that may just change my whole excel experience. @djclements 

While we're on the topic, are you aware of any way to link two workbooks such that you don't need both open but the 'child' workbook stays in sync within a minute or so of any update to the other? I'm essentially trying to make a read-only table without constantly manually refreshing. 

P.S. the other workaround I discovered is just to hide columns with a CNC Order # < 1 via conditional formatting.

[#4]

@G-BullMetal Maybe look into using Power Query to pull the data in from another file. On the Ribbon, go to Data > Get Data > From File > From Excel Workbook. After loading the desired data, you can set the Query properties to "Refresh every" n minutes. If you haven't used Power Query before, there are plenty of "How To" videos available on YouTube to help get you started.