advanced filtering issues

I have a problem when filtering.

I have three sheets in my workbook: Before Filter, After Filter, and Sheet1
Before Filter is the main data, After Filter is the data after its been filtered and Sheet 1 must run off the filtered data.

Problem:
When I filter the data, the row numbers change when filtered. What I mean is, when Excel filters the data it keeps the original row number on the left and doesn't move the filtered data up to row2 it just shows the original row number. So when I try to grab the data in Sheet1 it gives me all the data in its corresponding rows not just the filtered data.

What I need to do:
In the attached example, I need the data from, what should be A2, in the After Filter sheet to show in A1 of Sheet1, without making a direct reference to the row number that is shown in the second row in the After Filter sheet, and so on down the rows.

Can this be done without using Macros?
Please provide code examples in your replies.
Thanks

That's what I thought filtering did but I wan't sure.

Due to confidentiality I cannot post my whole project so I have been trying to ask the right questions to get done what I need to do. My project is a calendar that shows data in different rows due to specific criteria. I have been trying to filter down the data to individual tables to fill each row in the calendar from the desired table so I need to have consistent row numbers in the filtered tables. This calendar will be run everyday and will have different data everyday so I'm not sure how I will fill the calendar if I cannot directly reference A2, A3, A4, etc. because currently cells A2, A3, A4, etc. in the filtered table(even if not shown on the table) may or may not have the data I require. I hope this gives you a better understanding of what I am trying to accomplish.

Thanks for your help again today FDibbins!

I put the following statement into Sheet1 A1 and initialized it with CTRL+SHIFT+ENTER.
=IFERROR(INDEX(OFFSET(INDEX('Before Filter'!$C$2:$C$27, MATCH(TRUE,'Before Filter'!$O$2:$O$27,0)),,, COUNTIF('Before Filter'!$O$2:$O$27, TRUE), ), ROW('Before Filter'!A1)), "")

I changed the $L$2 references to TRUE because L2 may or may not be TRUE.
The output only gave me the first WO that was TRUE in A1 no other work orders were shown.
How do I get the rest of the WO numbers in subsequent rows into Sheet1?

Thanks for your help guys!
I used dredwolf's function since it shows all the data for each row and not just the WO #.
Once I pasted the code into A2 then I had to drag and highlight the extra cells to show all the data.

Thanks for your help on this!!!