Displaying only specified values-not using Autofilter

D

David Holman

I have a somewhat unusual question based on something that's stumped me
for over a week. I'm hoping the collective wisdom of this newsgroup
might be of some help.

I'm attempting to display all of the results of value "x" from one
sheet on another. To elaborate, sheet 2 has a column, let's call it
column A. Each cell in column A has a value, let's say, for simplicity
sake, "1", "2", "3", or "4". What I want to do is go back to sheet 1
and format it in such a way that all of the rows containing value "1"
in the aforementioned cell are displayed, however many rows that
happens to be, be it one or fifteen. Next to or below these values, I
want to display the same data, only this time using all of the rows
containing value "2", and so on.

Obviously, I can't use something like a standard filter, as all that
will serve to do is compress all of the nonconforming rows to size
zero, and that's not really conducive to my goal of being able to
display _all_ of the results, based on each distinct value (1, 2, 3, or
4). Does doing this require a macro or some similar type of Excel magic
that I simply don't have the experience to either derive or understand,
or am I missing something reasonably straightforward?

Any help that can be provided would be greatly appreciated. Thanks in
advance!
 
P

Pete_UK

You could copy your sheet (CTRL-drag the tab, or right-click on the
tab) and then sort it on the second sheet - all the "1"s will appear
first, followed by the "2"s etc.

Hope this helps.

Pete
 
D

David Holman

Two reasons, at least as near as I can tell. First, the number of rows
containing information is substantial; about 250 in fact. Only some of
those rows will contain the aforementioned values (1, 2, 3, etc.). To
provide a clearer example, say one is looking at items on a menu, or
classes at a University, or available workers. You might wish to select
'eggs', 'bacon', and 'sausage' for BREAKFAST, and 'steak' and
'potatoes' for DINNER, while omitting 'corn' and 'tofu' (using Data -->
Validation to create a list of options). Similar logic applies for
classes that someone might enroll in at University or workers who could
be assigned to Shift A,Shift B, and Shift C.

I'd like sheet one then to only display those selected items. So say
the first area of the spreadsheet could be formatted to display all
items that were selected for 'BREAKFAST' or "WINTER SEMESTER', or
"SHIFT A'. Then the next section of the spreadsheet, be it to the right
or further down the page, would display 'LUNCH', 'SHIFT B', etc. It
wouldn't be very valuable to first display, say, all of the breakfast
items, and then list the other 247 items just below that.

The second reason is simply aesthetic, and can be discarded if need be.
I want to create a consolidated sheet wherein selections taken from a
fairly large database are consolidated and made easy to view for the
user. That way, they can tell at a quick glance what items have been
selected for each and every period (be it meals, semesters, or worker
shifts) without being distracted by having to view the other 98% of the
material that they have no use for. In other words, I'm trying to
simplify their planning by enabling them to view every selection for
't' periods at once.
 
P

Pete_UK

What you are describing here is totally different to what you say in
your first posting, or perhaps I misunderstood. You seem to be saying
now that you want to make a selection from the "1"s on Sheet 1 and have
only that selection displayed/copied to Sheet 2, followed by the
selection from the "2"s etc.

Is this correct?

Pete
 
R

Roger Govier

Hi David

Take a look at Advanced Filter, extracting data to a second sheet.
Using Data Validation to give the user dropdowns to make their various
selections, then use some VBA code attached to a button to invoke the
filter, draw the records across and hide any columns of data that are
not really required, to provide an uncluttered view. I use this a lot.

For more information on how to use this and some downloadable sample
files, take a look at Debra Dalgleish's site
http://www.contextures.com/xladvfilter01.html#ExtractWs
 
D

David Holman

Roger,

Going through the website offered by Ms. Dalgleish proved
extraordinarily helpful. It seems I hadn't really understood the
Autofilter function before looking at the site (as I'd been using the
thing completely incorrectly). Now all I have to do is figure out how
to make these filtered records live, or at least instantly
re-generatable as the selected records change, something I suspect I
can work out by doing a little research, reviewing the samples on the
website, and/or working to come up with some VB code to incorporate
into a button that will accomplish the same thing as (if I'm reading
your reply correctly), you suggested. Thank you for taking the time to
explain this; it's very much appreciated!

--
Dave Holman


Roger said:
Hi David

Take a look at Advanced Filter, extracting data to a second sheet.
Using Data Validation to give the user dropdowns to make their various
selections, then use some VBA code attached to a button to invoke the
filter, draw the records across and hide any columns of data that are
not really required, to provide an uncluttered view. I use this a lot.

For more information on how to use this and some downloadable sample
files, take a look at Debra Dalgleish's site
http://www.contextures.com/xladvfilter01.html#ExtractWs
Click to expand...
 
D

David Holman

No, it's not different, rather it seems I just described what I was
trying to do really poorly the first time around. I'm sorry about that.
Roger pointed me to a good site elsewhere in this thread, and I _think_
I can use that to accomplish what I'm trying to do (which is in fact
what you deduced below). Thanks for your time and effort, and sorry
about the confusion!
 
Top