Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
bxb7668
 
Posts: n/a
Default Subset of one sheet on another sheet

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


  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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(befo=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

"bxb7668" wrote in message
...
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




  #3   Report Post  
Kassie
 
Posts: n/a
Default

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" wrote:

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



  #4   Report Post  
bxb7668
 
Posts: n/a
Default

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

"Kassie" wrote in message
...
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" wrote:

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Subset of one sheet on another sheet bxb7668 Excel Discussion (Misc queries) 3 April 25th 05 03:55 PM
Impoting data from Sheet 1 to Sheet 2 a-leano Excel Discussion (Misc queries) 1 April 20th 05 01:05 AM
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. RonMc5 Excel Discussion (Misc queries) 9 February 3rd 05 01:51 AM
linking multiple sheets to a summary sheet greg g Excel Discussion (Misc queries) 1 December 16th 04 08:43 AM
Naming & renaming a sheet tab Cgbilliar Excel Worksheet Functions 1 November 7th 04 06:57 PM


All times are GMT +1. The time now is 11:21 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"