Subset of one sheet on another sheet

[bxb7668]

I've been tasked to create an auction spreadsheet for my PTA to track
what has been donated; whether it's for the silent auction, raffle, or
live auction; and who wins it for how much. It needs to be as user
friendly as possible, as I cannot guarantee that future Auction
Committee member will be good with Excel. (A database would probably
be better, but we don't have one.) Since in the course of planning the
auction an item may be moved back and forth between the silent auction
and raffle and live auction many times, I want to be able to
automatically have another sheet that just lists Live Auction items
and another that lists Raffle items and another for Silent Auction
items. With this background, let me explain what I'd like to do and
ask if it is possible.

On the Inventory sheet I'd list the stuff that has been donated and
have a column that would have the auction type, i.e. silent, live or
raffle. Something like:
A B
1 Desc Auction
2 Boat Live
3 Candy Raffle
4 Dinner Silent
4 Cruise Live

There would then be three other sheets. One for each auction type.
The Live Auction sheet would use some function to just list the items
on the Inventory sheet that had been categorized as "Live". It should
end up looking like:

A B C D
1 Desc Item# Bidder Bid
2 Boat 101
3 Cruise 102

Is there a way to pull a subset of the rows from one sheet onto
another and not have blank lines for non-matching criterion? In other
words, I do not want for the Live Auction sheet to look like:

A B C D
1 Desc Item# Bidder Bid
2 Boat 101
3 102
4 103
5 Cruise 104

where rows 3 and 4 are blank because they are not Live.

Any help is greatly appreciated.
Brian Bygland

 

[Bernie Deitrick]

Brian,

In general, what you want to do is a BAD idea. It would be better to just
use one sheet, and apply a filter based on your column "Auction".

However, if you really want to do that, then run the macro below. It will
create three sheets based on your auction column. When the macro prompts
you, type in a 2 for the key column number.

Note, though, that you will still have some clean up to do: getting rid of
unwanted columns, or adding columns for information that is applicable to
only one subgroup.

HTH,
Bernie
MS Excel MVP


Sub ExportDatabaseToSeparateSheets()
'Export is based on the value in the desired column
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")


Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(before:=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell

End Sub

 

[Kassie]

The easiest way, as far as I can tell, would be to filter the list in place,
for either live, silent or raffle. In this way, you can either see the
entire list, or select what you want to see. Use Data|Filter and Auto Filter.

If you have to have it in seperate sheets, then you can add a formula to
cell A2 and B2of Sheets 2, 3 and 4: =Sheet1!A2 and =Sheet1!B2. Copy these
down as far as you want to go, and then apply a filter to these sheets, one
for silent, one for live and one for raffle. Although this will result in a
lot of duplication, you will achieve what you want, and you will only see the
relevant lines, while the rest will be hidden. Any other way will be fraught
with danger

 

[bxb7668]

Thank you Bernie and Kassie. I'll look into filtering. If it were me
I'd use one sheet and filter it. Unfortunately I have to assume that
whoever uses it in the future is not used to Excel, so I'll probably
need to use multiple sheets.

Brian