I'm going to work off a few assumptions:
- There is a cell, A1, on your report sheet where someone can enter the piece of equipment they are interested in.
- There are two columns, A and B, in your 'data collection' page: The equipment name and the y/n for completeness
- For now you only want to see 'completed' rows on your report page (this could be expanded with subtle changes to the formulas).
- Completed rows on the report page will start at Row 2
First, you need a way to identify that the equipment entered on your report tab's cell A1 matches each row on the datacollection's column A and that the work is complete in column B You could do this with an "and" formula in column C of your data collection tab.
=AND(A1=reference!$A$1, B1='Y')
This will return TRUE if both conditions are met and FALSE if either or both are not met.
Next we have to get a little higher level on this. We need get the TRUE rows from the data entry sheet over to the report sheet. We don't want the report sheet to have a bunch of blank rows in between the TRUE rows that are brought over. Otherwise we could just do a little vlookup or index/match on TRUE and call it a day. Instead, we can use RANK to establish the order in which the rows are brought over from the data collection page to the reference page.
To do this, in column D on the data collection sheet we need to RANK the TRUE's from 1 to whatever the count of TRUE rows is. Since there is no way to say that one TRUE row ranks higher than another, we will use ROW() for the row number to establish the rank. In Column D enter:
=IF(C1=TRUE, ROW(), 0)
All this will do is write the row in Column D when the criteria established in Column C is met. Now to add the RANK, in Column E enter:
=RANK(D1,D1:D14)*(D1>0)
This does two things. First it determines the rank of the row starting at 1 and going to whatever the number of "TRUE" rows there are in the first 14 rows (increase that to cover all of your data entry rows). Second the *(D1>0)
puts a 0 in column E if the value in Column D is 0 because we don't want to even consider the rank if it's a FALSE row.
Now you should have ranked values in Column E that are in descending order based on whether the criteria was TRUE and the row number. Now we need to drag these values over to the report sheet. To do this, in the report sheet starting at Cell A2 start numbering 1,2,3,4,... down the column. These will "match" up to the ranks in your data collection sheet in column E.
Now to get the first column of matching rows, enter the following in your report sheet's column B (starting at B1):
=INDEX(Sheet2!A:A, MATCH(Sheet1!A2, Sheet2!E:E,0), 1)
This is using the index function which allows you to pull the value from a specific row and column in a range. Our range is Sheet2!A:A (the data collection sheet's, column A). Our row is whichever rank (sheet2!E:E) value matches our numbers that we entered into our reporting sheet's column A. Our column, is just 1. Look up "index match in excel" in google to understand this one more if this is new to you. You can pull your data collection column B the same way. In column B of your report sheet (Starting at B2) enter the following:
=INDEX(Sheet2!B:B, MATCH(Sheet1!A2, Sheet2!E:E,0), 1)
That's it for my simple assumptions. Essentially you need three columns to determine whether your criteria was met in the data collection tab and then to rank that data from 1 to n. And you need to use index match (or vlookup if you rearrange things a bit) to pull those ranked rows to your reporting sheet. You can copy this same logic to rank the FALSE's in case you would like to bring over rows marked as incomplete in your data collection tab.
Lastly, after you wrap your head around this, consider moving columns C,D, E in the data collection tab to some other tab so the folks doing the data collection don't monkey up your formulas. The Index/Match function will still work fine and your data collection peeps won't see the gory details of your workbook's inner workings.