Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

In Excel 2007, I have a sheet full of data "A". One of the columns in sheet "B" is called "Valid" and has either "yes" or "no".

I've created a second sheet "B". It's easy to make each row in "A" appear in "B" if the row is valid using an 'if' statement in each cell. But if it's invalid, there's a blank row.

I need "B" to show only the rows from "A" that are valid. TWO BIG CAVEATS: - No macros - No filtering (for long and complicated reasons).

I feel like it might be possible with vlookup used cleverly, but so far, I'm stumped.

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

I don't think you can use VLookup to do what you want because it's not really a "query" in an SQL sense. Excel doesn't have a query language.

You can use MS Query under Data|Get External Data|From Microsoft Query.

Select your XLSX file as the source, then under Options, you'll need to select System tables to see the columns you created. Then you can build a simple query to select what you need. If your file is going to move around a lot, then the only way this would work would be to split the source and destination worksheets into separate XLSX files.

Another alternative might be a pivot table, with your Validity column in the Report Filter and your column A in the Row labels. Then you can filter out the invalid rows in the Report Filter.

share|improve this answer
    
Thank you! That is an extremely clever and totally bizzaro way to go about doing that. I don't think it'll work for my purposes because this spreadsheet is going to move around a lot, so the location of the target file for the query will change, unfortunately. –  user32706 Mar 30 '10 at 9:09
    
Thanks again! Your update will probably do the trick. –  user32706 Apr 1 '10 at 8:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.