Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting information from large worksheet
I have a large worksheet (thousands of rows), containing absence details for
all Areas in our Company. I have to send this worksheet out to each Area Manager showing their absence details for that month. However, rather than sending them the whole worksheet with all areas on, I want to create a workbook containing separate worksheets for each Area to make it easier for them to read because they are not advanced Excel users. The method I have used so far is the 'advanced filter'. By entering criteria, ie. Area 1' etc. onto a new worksheet and performing an advanced filter, this only shows those people who work in Area 1. This works perfectly fine, however, I wondered if there were any alternative ways that may be quicker, as I have to produce this on a monthly basis. I don't want to use the 'auto filter' as they can then use the filter arrows to view other Areas' information. Hope this makes sense? Any help would be appreciated. Thank you. Louise |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting information from large worksheet
Hi Louise
If you have sheet set up for each Area Manager, with the relevant criteria set up in the criteria area for Advanced Filter to extract the data that is relevant to him, then having update your Master sheet of all data, surely it is only a question of refresh for each sheet, then Mail or Print just that sheet for them. (I presume you are not sending the whole workbook, as you say you don't want them to see other area's data.) Alternatively, depending upon the layout of data in your Master sheet, you might be able to use a Pivot Table, with Area as a Page Field. From the resulting Pivot Table report, there is an Option to Show Pages, which would split out to separate sheets, just the data for that area. This could then be Printed / Mailed to them. If this is of interest, post detail of how your data is structured, and we may be able to help you construct a Pivot Table. -- Regards Roger Govier "Louise" wrote in message ... I have a large worksheet (thousands of rows), containing absence details for all Areas in our Company. I have to send this worksheet out to each Area Manager showing their absence details for that month. However, rather than sending them the whole worksheet with all areas on, I want to create a workbook containing separate worksheets for each Area to make it easier for them to read because they are not advanced Excel users. The method I have used so far is the 'advanced filter'. By entering criteria, ie. Area 1' etc. onto a new worksheet and performing an advanced filter, this only shows those people who work in Area 1. This works perfectly fine, however, I wondered if there were any alternative ways that may be quicker, as I have to produce this on a monthly basis. I don't want to use the 'auto filter' as they can then use the filter arrows to view other Areas' information. Hope this makes sense? Any help would be appreciated. Thank you. Louise |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting information from large worksheet
Hi Roger
Thank you for your reply. I'm not sure what you mean by 'updating the master'? I receive a different file each month (it's not the same one with amendments made to it) so there won't be any updates on the file - it's a completey new one. (Just out of curiosity though, when you create advanced filters, how do you update the filtered information if the original data changes??) with regards to your pivot table idea, i will give this a try also, see if the results are what we are looking for. Thank you. Louise "Roger Govier" wrote: Hi Louise If you have sheet set up for each Area Manager, with the relevant criteria set up in the criteria area for Advanced Filter to extract the data that is relevant to him, then having update your Master sheet of all data, surely it is only a question of refresh for each sheet, then Mail or Print just that sheet for them. (I presume you are not sending the whole workbook, as you say you don't want them to see other area's data.) Alternatively, depending upon the layout of data in your Master sheet, you might be able to use a Pivot Table, with Area as a Page Field. From the resulting Pivot Table report, there is an Option to Show Pages, which would split out to separate sheets, just the data for that area. This could then be Printed / Mailed to them. If this is of interest, post detail of how your data is structured, and we may be able to help you construct a Pivot Table. -- Regards Roger Govier "Louise" wrote in message ... I have a large worksheet (thousands of rows), containing absence details for all Areas in our Company. I have to send this worksheet out to each Area Manager showing their absence details for that month. However, rather than sending them the whole worksheet with all areas on, I want to create a workbook containing separate worksheets for each Area to make it easier for them to read because they are not advanced Excel users. The method I have used so far is the 'advanced filter'. By entering criteria, ie. Area 1' etc. onto a new worksheet and performing an advanced filter, this only shows those people who work in Area 1. This works perfectly fine, however, I wondered if there were any alternative ways that may be quicker, as I have to produce this on a monthly basis. I don't want to use the 'auto filter' as they can then use the filter arrows to view other Areas' information. Hope this makes sense? Any help would be appreciated. Thank you. Louise |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting information from large worksheet
Louise
Since you need to do this repeatedly (monthly), I would recommend that you consider doing it via VBA programming. The result would be a one-click type of thing. That is, one click of the mouse button and all the different workbooks/worksheets would be created for you. Post back if you think this might be what you need. Also, clarify what you mean by "worksheet" and "workbook". Workbook is the Excel file in its entirety. A worksheet is one sheet in the workbook. Exactly what is it that you want to send to each of the managers? A workbook containing only the sheets pertinent to that manager? Or a workbook showing everything but having each manager's area on separate worksheets? HTH Otto "Louise" wrote in message ... I have a large worksheet (thousands of rows), containing absence details for all Areas in our Company. I have to send this worksheet out to each Area Manager showing their absence details for that month. However, rather than sending them the whole worksheet with all areas on, I want to create a workbook containing separate worksheets for each Area to make it easier for them to read because they are not advanced Excel users. The method I have used so far is the 'advanced filter'. By entering criteria, ie. Area 1' etc. onto a new worksheet and performing an advanced filter, this only shows those people who work in Area 1. This works perfectly fine, however, I wondered if there were any alternative ways that may be quicker, as I have to produce this on a monthly basis. I don't want to use the 'auto filter' as they can then use the filter arrows to view other Areas' information. Hope this makes sense? Any help would be appreciated. Thank you. Louise |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting information from large worksheet
Hello and thank you for your reply.
If you know how to write the code for this type of thing, that would be extremely useful. I have extremely limited knowledge of VBA so wouldn't be able to write it myself. Yes, I am aware that the workbook is the whole thing and inside it are the individual sheets. I want to create one workbook, containing different sheets for the different areas so each Area Manager can look at their own details easily and, if necessary, can easily view the others' too. Thank you. Louise "Otto Moehrbach" wrote: Louise Since you need to do this repeatedly (monthly), I would recommend that you consider doing it via VBA programming. The result would be a one-click type of thing. That is, one click of the mouse button and all the different workbooks/worksheets would be created for you. Post back if you think this might be what you need. Also, clarify what you mean by "worksheet" and "workbook". Workbook is the Excel file in its entirety. A worksheet is one sheet in the workbook. Exactly what is it that you want to send to each of the managers? A workbook containing only the sheets pertinent to that manager? Or a workbook showing everything but having each manager's area on separate worksheets? HTH Otto "Louise" wrote in message ... I have a large worksheet (thousands of rows), containing absence details for all Areas in our Company. I have to send this worksheet out to each Area Manager showing their absence details for that month. However, rather than sending them the whole worksheet with all areas on, I want to create a workbook containing separate worksheets for each Area to make it easier for them to read because they are not advanced Excel users. The method I have used so far is the 'advanced filter'. By entering criteria, ie. Area 1' etc. onto a new worksheet and performing an advanced filter, this only shows those people who work in Area 1. This works perfectly fine, however, I wondered if there were any alternative ways that may be quicker, as I have to produce this on a monthly basis. I don't want to use the 'auto filter' as they can then use the filter arrows to view other Areas' information. Hope this makes sense? Any help would be appreciated. Thank you. Louise |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting information from large worksheet
Hi Otto, thank you for all your help.
I have sent you a copy of the data i have to work with. Any help you can offer would be appreciated. Louise "Otto Moehrbach" wrote: Louise The code I would write would depend greatly on the layout of your data. If you wish, send me your file or an example of your file. I need only the layout of what you have and what you want to get as the final product. Fake your data as you see fit with fake names, etc if you feel your data is proprietary. Include an explanation/example of what you have at the start and what you want to have at the end. For instance, do you always start with a new file and you need to create the new sheets every month, or do you use the same file and just update it for every month? If you have the latter, perhaps the "manager" sheets can be a part of the file all the time and the code will just update those sheets. My email address is . Remove the "nop" from this address. HTH Otto "Louise" wrote in message ... Hello and thank you for your reply. If you know how to write the code for this type of thing, that would be extremely useful. I have extremely limited knowledge of VBA so wouldn't be able to write it myself. Yes, I am aware that the workbook is the whole thing and inside it are the individual sheets. I want to create one workbook, containing different sheets for the different areas so each Area Manager can look at their own details easily and, if necessary, can easily view the others' too. Thank you. Louise "Otto Moehrbach" wrote: Louise Since you need to do this repeatedly (monthly), I would recommend that you consider doing it via VBA programming. The result would be a one-click type of thing. That is, one click of the mouse button and all the different workbooks/worksheets would be created for you. Post back if you think this might be what you need. Also, clarify what you mean by "worksheet" and "workbook". Workbook is the Excel file in its entirety. A worksheet is one sheet in the workbook. Exactly what is it that you want to send to each of the managers? A workbook containing only the sheets pertinent to that manager? Or a workbook showing everything but having each manager's area on separate worksheets? HTH Otto "Louise" wrote in message ... I have a large worksheet (thousands of rows), containing absence details for all Areas in our Company. I have to send this worksheet out to each Area Manager showing their absence details for that month. However, rather than sending them the whole worksheet with all areas on, I want to create a workbook containing separate worksheets for each Area to make it easier for them to read because they are not advanced Excel users. The method I have used so far is the 'advanced filter'. By entering criteria, ie. Area 1' etc. onto a new worksheet and performing an advanced filter, this only shows those people who work in Area 1. This works perfectly fine, however, I wondered if there were any alternative ways that may be quicker, as I have to produce this on a monthly basis. I don't want to use the 'auto filter' as they can then use the filter arrows to view other Areas' information. Hope this makes sense? Any help would be appreciated. Thank you. Louise |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ignoring Rows When Extracting Data From One Worksheet To Another | Excel Worksheet Functions | |||
Extracting information from records to another sheet automatically | Excel Worksheet Functions | |||
Pulling in Information from a worksheet | Excel Worksheet Functions | |||
Extracting data from one Worsheet to Another Worksheet with common link value | Excel Discussion (Misc queries) | |||
Extracting same column from large number of workbooks | Excel Worksheet Functions |