Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default Master & Sub Worksheets?

Question:

Worksheet One has all the data for all the groups.

Worksheet Two through €¦ contains portions of the data in Worksheet One. The
portions are predefined ranges of values that appear in the Group column in
Worksheet One. The values in the Group column are sorted so that all of the
As, Bs, Cs, etc. are listed in rows grouped together.

Solution:

My thoughts run in the lines of a query. The query, on Worksheet Two would
select Group values of A through D. The query, on Worksheet Three would
select Group values of E through G. etc.

I do this now with Cut/Paste but there has got to be a better way.

Can you point me in the correct direction?

  #2   Report Post  
Posted to microsoft.public.excel.newusers
CLR CLR is offline
external usenet poster
 
Posts: 594
Default Master & Sub Worksheets?

Consider using the AutoFilter, automated with a macro if desired.......

Vaya con Dios,
Chuck, CABGx3


"idmnstr" <u32471@uwe wrote in message news:6f2d582c3dd5b@uwe...
Question:

Worksheet One has all the data for all the groups.

Worksheet Two through . contains portions of the data in Worksheet One.

The
portions are predefined ranges of values that appear in the Group column

in
Worksheet One. The values in the Group column are sorted so that all of

the
As, Bs, Cs, etc. are listed in rows grouped together.

Solution:

My thoughts run in the lines of a query. The query, on Worksheet Two

would
select Group values of A through D. The query, on Worksheet Three would
select Group values of E through G. etc.

I do this now with Cut/Paste but there has got to be a better way.

Can you point me in the correct direction?



  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Master & Sub Worksheets?

CLR wrote:
Consider using the AutoFilter, automated with a macro if desired.......

Vaya con Dios,
Chuck, CABGx3


Chuck,

Thank You for your response. I am sorry it took s long to post this reply
but I did not get your response emailed to me. I have it set up that way but
...

I tried AutoFilter;
- This means that each worksheet would have the same number of rows but only
showing the ones I want. This works but the workbook, an accumilation of all
the worksheets, may be a little large.

One thing that I did not include in my original post. I then create Pivot
Tables from these Sub Worksheets. When I attempt to do this with the
"AutoFilter" sub worksheet I get everything included and not the filtered
data.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200703/1

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Master & Sub Worksheets?

Chuck,

Thanks again. A macro might be the answer I am looking for if I have to
copy/paste the rows.

There isn't any way to reference the first sheet from the second based on a
range of values in a column on the first sheet? The problem is that the
coulmn is the same but not always the same rows.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200703/1

  #5   Report Post  
Posted to microsoft.public.excel.newusers
CLR CLR is offline
external usenet poster
 
Posts: 594
Default Master & Sub Worksheets?

Sorry, what I meant is to only have the entire database on the first main
sheet, and do the Autofilter there, and then copy and paste that filtered
data over to your other sheets..........

This can all eventually be done by macro at the push of a button, including
making the Pivot tables if desired......

Vaya con Dios,
Chuck, CABGx3


"idmnstr via OfficeKB.com" <u32471@uwe wrote in message
news:6f46689d7b3f1@uwe...
CLR wrote:
Consider using the AutoFilter, automated with a macro if desired.......

Vaya con Dios,
Chuck, CABGx3


Chuck,

Thank You for your response. I am sorry it took s long to post this reply
but I did not get your response emailed to me. I have it set up that way

but
..

I tried AutoFilter;
- This means that each worksheet would have the same number of rows but

only
showing the ones I want. This works but the workbook, an accumilation of

all
the worksheets, may be a little large.

One thing that I did not include in my original post. I then create Pivot
Tables from these Sub Worksheets. When I attempt to do this with the
"AutoFilter" sub worksheet I get everything included and not the filtered
data.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200703/1





  #6   Report Post  
Posted to microsoft.public.excel.newusers
CLR CLR is offline
external usenet poster
 
Posts: 594
Default Master & Sub Worksheets?

Try to do an Tutofilter on your master sheet. Then highlight the entire
range of cells from the first cell below your header row in column A all the
way down and across to the right as far as you have visible data. Ignore
the row numbers. then do Copy and Paste this data to your second
sheet.....then on that sheet you will only have the data represented by the
Autofilter done on the master sheet, not including the rows between. Then
do the same thing for another setting of the Autofilter, and copy and paste
it to another sheet, etc etc........then all those sheets will only contain
the data represented by their unique Autofilter settings........then you can
do routine analysis with formulas or Pivot tables etc on each sheet and you
will only be dealing with THAT data, not the entire database..........

hth
Vaya con Dios,
Chuck, CABGx3


"idmnstr via OfficeKB.com" <u32471@uwe wrote in message
news:6f4783ecc2c72@uwe...
Chuck,

Thanks again. A macro might be the answer I am looking for if I have to
copy/paste the rows.

There isn't any way to reference the first sheet from the second based on

a
range of values in a column on the first sheet? The problem is that the
coulmn is the same but not always the same rows.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200703/1



  #7   Report Post  
Posted to microsoft.public.excel.newusers
CLR CLR is offline
external usenet poster
 
Posts: 594
Default Master & Sub Worksheets?

LOL.......note that a "Tutofilter" is really an Autofilter in
disguise......type-O, sorry.

Vaya con Dios,
Chuck, CABGx3


"CLR" wrote in message
...
Try to do an Tutofilter on your master sheet. Then highlight the entire
range of cells from the first cell below your header row in column A all

the
way down and across to the right as far as you have visible data. Ignore
the row numbers. then do Copy and Paste this data to your second
sheet.....then on that sheet you will only have the data represented by

the
Autofilter done on the master sheet, not including the rows between. Then
do the same thing for another setting of the Autofilter, and copy and

paste
it to another sheet, etc etc........then all those sheets will only

contain
the data represented by their unique Autofilter settings........then you

can
do routine analysis with formulas or Pivot tables etc on each sheet and

you
will only be dealing with THAT data, not the entire database..........

hth
Vaya con Dios,
Chuck, CABGx3


"idmnstr via OfficeKB.com" <u32471@uwe wrote in message
news:6f4783ecc2c72@uwe...
Chuck,

Thanks again. A macro might be the answer I am looking for if I have to
copy/paste the rows.

There isn't any way to reference the first sheet from the second based

on
a
range of values in a column on the first sheet? The problem is that the
coulmn is the same but not always the same rows.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200703/1





  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,886
Default Master & Sub Worksheets?

Hi

In addition to Chuck's suggestion of using Autofilter, I use Advanced
Filter on occasions to do just what you are trying to do.
You can use Advanced Filter to extract data to another sheet, provided
you begin the Advanced Filter set up from the Destination sheet, not the
source (Master) sheet.

Debra Dalgleish has some excellent instruction on how to this on her
site, and some example files that can be downloaded. She also shows code
on how to automate the procedures.
http://www.contextures.com/xladvfilter01.html#ExtractWs

If you download the following file, it will show you how to do just what
you require
http://www.contextures.com/AdvFilterRepFiltered.zip

--
Regards

Roger Govier


"idmnstr via OfficeKB.com" <u32471@uwe wrote in message
news:6f4783ecc2c72@uwe...
Chuck,

Thanks again. A macro might be the answer I am looking for if I have
to
copy/paste the rows.

There isn't any way to reference the first sheet from the second based
on a
range of values in a column on the first sheet? The problem is that
the
coulmn is the same but not always the same rows.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200703/1



  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Master & Sub Worksheets?

Roger,

Thank you that pointer. The example is a good one but no really what I am
looking for a solution. I will browse this page some more to gain some
additional insight.

The number of rows is not always the same. If I have to tell it each time
the new row number then to me it is the same as a manual cut/paste solution.

That is why I was looking for a query type solution. This way when the value
in the column changes to be outside my desired range then I know I have all
my rows.

Once I have my subset of rows then I need to create a piovt table from them.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200703/1

  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Master & Sub Worksheets?

Chuck,

Thank again for the pointer.

I took a look at the Zip file. Yes, This is 90 % of what I am looking for.

I am not refering to the Pivot Table part as being the missing 10 %. I do
understand that that part is beyond the scope of the example.

When I say 90 % I make reference to the fact that the macro looks for unique
values within a column in order to create the sub sheets. Yes, the values
are in only one column but there are not unique values but a value range.

I know the values that constitue a sub sheets in advance and this can be
placed on sheet one. In the example you posted the ranges might be like;
Start End

Anderson Jardine
Jones Thomson
Smith Mogan
Sorvino Sorvino

Where each start & end would be a sub sheet.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200703/1



  #11   Report Post  
Posted to microsoft.public.excel.newusers
CLR CLR is offline
external usenet poster
 
Posts: 594
Default Master & Sub Worksheets?

With the Autofilter, one can choose the Custom option instead of selecting a
specific value, and with the "GreaterThan and LessThan" selections one can
return a range of values........You can also group names with a code number
in a helper column and filter on that column. Once filtered and copied to
another sheet, the Pivot table can be formed as part of the macro also.

Vaya con Dios,
Chuck, CABGx3


"idmnstr via OfficeKB.com" <u32471@uwe wrote in message
news:6f6b31e299471@uwe...
Chuck,

Thank again for the pointer.

I took a look at the Zip file. Yes, This is 90 % of what I am looking

for.

I am not refering to the Pivot Table part as being the missing 10 %. I do
understand that that part is beyond the scope of the example.

When I say 90 % I make reference to the fact that the macro looks for

unique
values within a column in order to create the sub sheets. Yes, the values
are in only one column but there are not unique values but a value range.

I know the values that constitue a sub sheets in advance and this can be
placed on sheet one. In the example you posted the ranges might be like;
Start End

Anderson Jardine
Jones Thomson
Smith Mogan
Sorvino Sorvino

Where each start & end would be a sub sheet.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200703/1



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
Master Worksheet populated by other Worksheets? Steve Lundwall Excel Worksheet Functions 0 March 3rd 06 02:38 PM
Link Worksheets to a Master Sheet Reefaman Excel Worksheet Functions 0 March 1st 06 08:19 PM
Master Worksheet reflecting sub-worksheets [email protected] New Users to Excel 10 February 25th 06 03:49 AM
Master worksheets? Marty Mcfly Excel Worksheet Functions 0 February 14th 06 05:33 PM
Linking worksheets using a master document jo Excel Discussion (Misc queries) 0 January 25th 06 12:41 AM


All times are GMT +1. The time now is 09:42 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"