Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Master Worksheet populated by other Worksheets? | Excel Worksheet Functions | |||
Link Worksheets to a Master Sheet | Excel Worksheet Functions | |||
Master Worksheet reflecting sub-worksheets | New Users to Excel | |||
Master worksheets? | Excel Worksheet Functions | |||
Linking worksheets using a master document | Excel Discussion (Misc queries) |