Excel 2007 : In the Shelly Cashman Series book for Excel 2007

**PennState** · 02-12-2010, 07:21 PM

If you have the book, I'm working on the in the lab question # 3, part 3, pages 412 and 413.

I have extracted the records based on these criteria:
1. Gender = F, GPA = >3.50
2. Age > 23
3. Gender = M, Age = <21

I'm now stuck on the part where it asks for me to extract records based on this criteria:
21<Age<25.

So far I have added the additional AGE column to the immediate right in my Criteria table and redefined my Criteria range in the name box. However, when I go back to the Advanced Filter dialog box and click OK, I get no results based on that criteria.

Any help is appreciated.

**Richard Buttrey** · 02-12-2010, 07:38 PM

Hi,

I haven't go the book but if you have an AND criteria, i.e. where the ages selected must be >21 and < 25 then you'll need a 2 x 2 criteria range with Age in two adjacent column cells with <25 in the first cell of the second row and >25 in the second cell of the second row.

e.g.

Please Login or Register  to view this content.

HTH

**PennState** · 02-12-2010, 07:41 PM

So I would put the >21 in one row and the <25 in the row beneath it as my range? That right?

I tried it that way and now instead of the whole table disappearing, the whole table stays. It still doesn't show just the 4 records.

**Richard Buttrey** · 02-12-2010, 08:06 PM

Hi,

First of all are you trying to extract the records to another range, or filtering the existing table? Your first post suggested the former, but your use of the phrase 'the whole table stays' in the latest post suggests the latter. My advice was covering the extracting to another area.

The >21 and the <25 go on the same row both immediately under cells containing the word 'Age'. Exactly as I showed in my first post. This two by two range must be specified as the criteria range in the Data Filter Advanced Filter process.

HTH

**PennState** · 02-12-2010, 08:14 PM

Sorry...yes I'm extracting the results from the table to another part of the book.

I have my table with the data and above that is my Criteria area. It's my understanding based on the first 3 records I extracted that once I defined my criteria and went to the Advanced Filter dialog box and clicked OK, the data in my table would update to show only those records that met the listed criteria. I would then extract those results to another part of my workbook.

Where I'm at now is that I can't get my data table to update with the criteria so that I can extract it. According to the book, there should be 4 results that remain after I input my criteria of 21<Age<25.

I've attached the work I've done so far, hopefully that might help my explanation some.

**PennState** · 02-13-2010, 12:53 PM

Still stuck here...haha

**Richard Buttrey** · 02-13-2010, 01:25 PM

Hi,

See the attached.

The first point to make is that unless you restrict the output range to a specific range of cells you are always in danger of over-writing anything that is below your output range labels. That's why it's always a good idea to keep them away from any other data and the easiest way to do this is on an individual sheet. I've added the sheet called Output.

In the Output sheet I have named the range E1:F2 'crit_age' and A2:H9 'DataOut'
Your table on the first sheet already has a name called 'Table1'

When you click the button the following macro is run.

Please Login or Register  to view this content.

i.e this does automatically what you can manually do through the menu Data Advanced Filter.....

Change the criteria to see the different effects. currently with >21 and <25 it gives you four output rows.

Since it contains a macro it's a .xlsm file rather than just a .xlsx file so you may need to accept the warning about it containing macros when youj open it. Depends how your system is set up.

HTH