Select Specific Rows Dependent on Criteria (Not Auto-Filter)

I want to look for rows on sheet 1 that have a specific value in column C and show only those rows on sheet 2.

Auto filter is not an option as I need sheet 2 to be the base data for a report that sheet 1 draws from an external source.

(I wanted to post an example s/sheet but can't for some reason).

You could record/write a macro that filters the data, copies only those rows and pastes them in Sheet 2.

Would that work for you at all?

BSB

Try ..

=IFERROR(INDEX(Sheet1!$B$1:$B$1000,SMALL(IF(Sheet1!$C$1:$C$1000="a",ROW($B$1:$B$1000)-ROW($B$1)+1,""),ROWS($B$1:B1))),"")

Place in Sheet2

Enter with Ctrl+Shift+Enter and copy down as far as required.

Will put values from Column B where column C has value of "a"

Without seeing your data, you could either amend INDEX range (B) to other columns you wish to extract or if you have a "key" field use VLOOKUP to transfer other data.