autofiltering over a range

B

bluegirl

I am not sure if autofilter is what I want to use or not.

I have 5 different sections (all on one sheet) each of which contain
between 3-15 columns.

The concept is this:

when a student takes an exam, the exam is listed in a row with thei
name, exam name, and other info. Then a tick mark is placed in th
square below the appropriate requirement (along the top row) showin
that this class fulfills that requirement. So you might have severa
rows of data with various tick marks (we are using the number 1) place
throughout (some requirements are duplicated for variou
certifications).

I have named the "data areas" appropriately according to wha
certification they are for. But if I use autofilter on one column, i
eliminates the other columns for that section. is there a way to sho
all rows that have a 1 in any of the columns for that range?

Someone on another board suggested using the SUM command, but I don'
see how that would work, but am going to experiment with it anywa
while I await divine intellectual input from someone here!
:cool:

Please let me know if you need me to elaborate or even send you a
example of what I am talking about.

I have been working on this for HOURS and can't seem to find a viabl
solution.

Eventually, I want to be able to show the final results in pivo
tables
 
P

Peter Atherton

Generally, you place all the information for one student
on one row and place the data under headings. e.g.
Id, Name, Math, MMArks, English, EMarks, etc.

This enables Excel to filter the data and for you to
create pivot tables.

To work with the data as you have try entering a number
for a student in column A (Insert, Columns if necessary)
and try filtering on that column.


[email protected]

Regards
Peter
 
A

AlfD

Hi!
But if I use autofilter on one column, it eliminates the other column
for that section
Click to expand...

Two things spring to mind here: you mention you have 5 data areas
presumably with blank columns between?

Second: when you set up the filter, do you highlight the whole sectio
you want to filter, or do you click on the header of one column? Sound
a bit as though it is the latter, which won't help you. Click on a cel
in the region you want to filter: ctrl + * to highlight you
"database". Then filter.

Al
 
D

Dave Peterson

But that ctrl-* will stop at the blank column, too.

I like to select all my range (bottomleft cell to topright cell), then
data|filter.
 
B

bluegirl

Well.... that didn't work either. LOL

OK... I have a pivot table set up where I can just drag over the
various requirements for the certifications, and will do a different
pivot table for each cert.

BUT... I am running into a rather strange problem. Any cell that
doesn't have the (1) mark in it comes up showing (blank). Literally.
That is what is shown in the column, (blank).

Is there a way to make it just be truly blank?
 
B

bluegirl

well cancel that because the second set of data is too large for a data
table.

(BANGING HEAD ON WALL)


THERE HAS TO BE A WAY TO GET SOME USEFUL INFO OUT OF THIS DANG THING!

Maybe if there is a way to pull the entire row (to a certain point)
onto another sheet if any of the columns I through S for in row A2
contain a 1??????
 
D

Dave Peterson

If you want to just change the display from "(blank)" to "" (looks empty), you
could adjust the raw data.

I like to put ="" in those blank cells. The cell still looks empty on the
worksheet, but you can see some funny formula in the formula bar. Usually
that's enough to remind me that I did something strange (and not fix it!).

Select your column in your raw data.
Edit|Goto (or F5 or Ctrl-G)
click special
click Blanks, then ok

Now, just your blank cells are selected.

type ="" and hit ctrl-enter to fill each cell in that range.

Back to your pivottable and hit refresh.

(If you just type an apostrophe and then ctrl-enter, it'd work ok, too. But I
like that formula as a memory jogger.)
 
Top